Обсуждение: quoting internal variable names

Поиск
Список
Период
Сортировка

quoting internal variable names

От
Ron Peterson
Дата:
Hi,

I'm trying to use PostgreSQL's internal variables to simplify some shell
scripting database setup stuff.  Single quotes appear to behave
differently in diffent contexts.


CREATE USER
  :v_dbadmin
WITH PASSWORD
  ':v_dbpass';
CREATE USER

....CREATE USER worked o.k.


CREATE DATABASE
  :v_dbname
WITH OWNER
  :v_dbadmin
ENCODING
  ':v_encoding';
ERROR:  :v_encoding is not a valid encoding name

....here the quotation marks appear to throwing things off


:v_encoding is set to SQL_ASCII.  If I remove the quotation marks, then
I (appropriately enough) get the error:

CREATE DATABASE
  :v_dbname
WITH OWNER
  :v_dbadmin
ENCODING
  :v_encoding;
ERROR:  syntax error at or near "SQL_ASCII" at character 59
LINE 6:   SQL_ASCII;


So it seems like the single quotes are causing :v_encoding to be read as
a string literal for ENCODING, but they don't do that for WITH PASSWORD.

?

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

Re: quoting internal variable names

От
Ron Peterson
Дата:
On Sat, Feb 19, 2005 at 05:30:25PM -0500, Ron Peterson wrote:
> Hi,
>
> I'm trying to use PostgreSQL's internal variables to simplify some shell
> scripting database setup stuff.  Single quotes appear to behave
> differently in diffent contexts.


I decided to just do the proper quoting within the value contained by
the variable, and that works fine.

If I'm not mistaken, in my example below, the single quote behaviour for
ENCODING is in fact correct (:v_encoding should be read as a string
literal), and whatever is going on for WITH PASSWORD is incorrect.  Is
that right?

Not a big deal, I have things working, I'd just like to know if I'm
mixed up about something here.

Best.

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso



> CREATE USER
>   :v_dbadmin
> WITH PASSWORD
>   ':v_dbpass';
> CREATE USER
>
> ....CREATE USER worked o.k.
>
>
> CREATE DATABASE
>   :v_dbname
> WITH OWNER
>   :v_dbadmin
> ENCODING
>   ':v_encoding';
> ERROR:  :v_encoding is not a valid encoding name
>
> ....here the quotation marks appear to throwing things off
>
>
> :v_encoding is set to SQL_ASCII.  If I remove the quotation marks, then
> I (appropriately enough) get the error:
>
> CREATE DATABASE
>   :v_dbname
> WITH OWNER
>   :v_dbadmin
> ENCODING
>   :v_encoding;
> ERROR:  syntax error at or near "SQL_ASCII" at character 59
> LINE 6:   SQL_ASCII;
>
>
> So it seems like the single quotes are causing :v_encoding to be read as
> a string literal for ENCODING, but they don't do that for WITH PASSWORD.
>
> ?
>
> --
> Ron Peterson
> Network & Systems Manager
> Mount Holyoke College
> http://www.mtholyoke.edu/~rpeterso
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Re: quoting internal variable names

От
Peter Eisentraut
Дата:
Ron Peterson wrote:
> Single quotes appear to behave
> differently in diffent contexts.
>
> CREATE USER
>   :v_dbadmin
> WITH PASSWORD
>   ':v_dbpass';

This sets your password to ":v_dbpass", which is probably not what you
wanted.  Your next message contains the correct solution: include the
quotes in the variable value.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: quoting internal variable names

От
Tom Lane
Дата:
Ron Peterson <rpeterso@mtholyoke.edu> writes:
> So it seems like the single quotes are causing :v_encoding to be read as
> a string literal for ENCODING, but they don't do that for WITH PASSWORD.

Yeah, they behave the same in both cases.  If you'd experimented you
would have found that the password was set to ":v_dbpass", presumably
not what you wanted, but it's OK as far as CREATE USER knows.

            regards, tom lane