1

I'm currently trying to use a bash variable to PSQL functions, I run the PSQL file in bash script like so...

psql -h *DBHOST* -p *DBPORT* -U *DBUSER* -d *DBNAME* -f ./file.sql \
    -v var1=$bash_var1 -v var2=$bash_var2

In the psql file, I can access the variables through...

:'var1' and :'var2'

I tried it in a function, here is a portion of the function

CREATE OR REPLACE FUNCTION rename()
    RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
    title TEXT;
    newTitle TEXT;
    docId TEXT;
    new_docId TEXT;
    var1 INT;
    var2 TEXT;
BEGIN
    CREATE TABLE IF NOT EXISTS files_renamed (
    uuid        char(40) ,
    name char (250),
    origin char (20)
    );
    FOR _r IN SELECT * from table LOOP
        docId := _r.id ;
        title := _r.title ;
        SELECT substring(docId, 1, doc_id_len) INTO new_docId;
        SELECT CONCAT(new_docId,:'var2') INTO newTitle;
        -- other codes --
    END LOOP;
END
$func$;

But inside this PSQL function, it didn't work. It returned this error...

ERROR: syntax error at or near ":"

Does anyone have an idea on how to do this correctly? Thanks, guys.

yacc
  • 2,915
  • 4
  • 19
  • 33
  • I'm no PSQL user but it seems that you need to pass the variable to the function body in the right way. That means, you'll probably need to put the string in quotes. And it would be helpful to give some more detailed example code. – yacc Aug 14 '17 at 07:26
  • Try with -v var1=a${bash_var1}b to see if your function receives 'ab' – netizen Aug 14 '17 at 07:47
  • 1
    Please [edit] the question to include the actual PSQL function. – tripleee Aug 14 '17 at 07:49
  • I edited the question –  Aug 14 '17 at 07:55
  • Outside the function, i tried 'SELECT :'var1'; and it worked, but inside the function it returned an error. –  Aug 14 '17 at 08:00
  • You can not pass `psql` variable with `-v` into `stored procedure`. The `-v` is same as doing `\set` ( [read documenation](https://www.postgresql.org/docs/current/static/app-psql.html) ). If you really *must* collect values via bash, write a bash script to e.g. generate correct SQL files for executing your stored procedure. Also, what's the point of stored procedure if you feed variable value from bash into procedure code directly? I'd really reconsider/revisit your task execution design overall ;) – Kristo Mägi Aug 14 '17 at 08:07
  • I'm sorry if I took too much of your time. I added some portion of the function –  Aug 14 '17 at 09:25

1 Answers1

0

You need to use dynamic SQL. That is, put the whole function block into a string and pass the quoted script variables to the appropriate places, then EXECUTE that string. See also this SO topic (Postgres Dynamic Query Function) on how to build the statement.

EXECUTE
 'CREATE OR REPLACE FUNCTION rename()' ||
 ... || :'var1' || ... || 'END'
yacc
  • 2,915
  • 4
  • 19
  • 33