How to execute a procedure call in postgresql using shell script?

Question

I need to execute a procedure in postgresql using shell script.

psql (9.2.13, server 11.1)

Please check the below details.

[genadmin@app7cn scripts]$ echo "var x clob;
> var y number;
> exec sysreg.WRITEVALUE_1('IPF_ADMIN','/Infinys/Installer/Installed/PF/InstallParameters/PF_CONTAINER_USER_PASSWORD','$encrypted_pwd',null, :x, :y);
> rem print :x :y
> "|PGPASSWORD=dbpasswd psql -h dbhost -d db_sid -U dbuser

Code:

if [ ! -z "${staged_enc}" ]; then
        echo "
        set heading off;
        set lines 1000
        set feedback off;
        set serveroutput on;
        var x clob;
        var y number;
        exec sysreg.WRITEVALUE_1('$dbuser','/Infinys/Installer/Staged/PF/InstallParameters/PF_CONTAINER_USER_PASSWORD','$encrypted_pwd', null, :x, :y);
        rem print :x :y
        " | sql
   fi

This is written for oracle. I need to change convert it for postgresql.

Getting below error:

ERROR:  syntax error at or near "var"
LINE 1: var x clob;
        ^
ERROR:  syntax error at or near "var"
LINE 1: var y number;
        ^
ERROR:  syntax error at or near "exec"
LINE 1: exec sysreg.WRITEVALUE_1('IPF_ADMIN','/Infinys/Installer/Ins...
        ^
ERROR:  syntax error at or near "rem"
LINE 1: rem print :x :y

Laurenz Albe · Accepted Answer · 2019-09-10 07:07:21Z

1

First, upgrade psql to something less ancient than 9.2.

I would use a DO statement here:

psql -h dbhost -d db_sid -U dbuser <<EOF
DO
\$\$DECLARE
   x integer;
   y integer;
BEGIN
   CALL myprocedure(x, y);
   RAISE NOTICE '%, %', x, y;
END;\$\$
EOF

answered Sep 10, 2019 at 7:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

This code should also work with psql 9.2. Recommended is the latest version.

Collectives™ on Stack Overflow

How to execute a procedure call in postgresql using shell script?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related