postgresql - error trying to pass a variable from command line to a sql script

Question

How do I pass in a variable into this postgresql code? I know it works for the first 2 lines and those variables, but then it errors going into the DO statement..

I'm using :p5 as my parameter.

CREATE ROLE :v3 LOGIN ENCRYPTED PASSWORD :p3 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
ALTER ROLE :v3 SET search_path = foo_bar_dev_:v4;
DO
$body$
BEGIN
   IF NOT EXISTS (
      SELECT *
      FROM   pg_catalog.pg_user
      WHERE  usename = 'foo_readonly') THEN

      CREATE ROLE foo_readonly LOGIN PASSWORD :p5 ;
   END IF;
END
$body$

I'm getting this error message:

psql:Test.sql:18: ERROR:  syntax error at or near ":"
LINE 9:       CREATE ROLE foo_readonly LOGIN PASSWORD :p5 ;
                                                      ^

Once it gets into the DO body is when I get this error.

I'm calling it this in a shell script like so:

"$PSQL" -h $HOST_NM             \
        -p $PORT                \
        -U foo                  \
        -v v3=$USER             \
        -v p3="'$USER_PWD'"     \
        -v p4="'$1'"            \
        -v p5="'$READONLY_PWD'" \
        -a                      \
        -f Test.sql postgres | tee >> $LOG

Is there another way to do this?

try LOGIN PASSWORD ':p5' ; instead?..

Vao Tsun
– Vao Tsun

2015-06-30 06:01:45 +00:00
Commented Jun 30, 2015 at 6:01 — Vao Tsun
– Vao Tsun, Commented Jun 30, 2015 at 6:01

Vao Tsun · Accepted Answer · 2015-06-30 08:01:06Z

1

password should be in quotes:

CREATE ROLE :v3 LOGIN ENCRYPTED PASSWORD :p3 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
ALTER ROLE :v3 SET search_path = foo_bar_dev_:v4;
DO
$body$
BEGIN
   IF NOT EXISTS (
      SELECT *
      FROM   pg_catalog.pg_user
      WHERE  usename = 'foo_readonly') THEN

      CREATE ROLE foo_readonly LOGIN PASSWORD ':p5' ;
   END IF;
END
$body$

answered Jun 30, 2015 at 8:01

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql - error trying to pass a variable from command line to a sql script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related