Using a variable in a function with sql and sh script

Question

I am developing a set of .sql scripts which will be called by a .sh script. In this .sh script, I use the command:

psql "connection parameters" -f ./myscript.sql -v var1 = "schema.table"

For the moment everything is well.

In my .sql script, I currently have this:

CREATE or replace FUNCTION myFunction (tarteenpion varchar) RETURNS void AS $$
DECLARED
   MONTH_MM varchar: = to_char (current_timestamp, 'MM');
   YEAR_AAAA varchar: = to_char (current_timestamp, 'YYYY');
   YEAR_AA varchar: = to_char (current_timestamp, 'YY')
   cmd varchar: = 'DROP TABLE IF EXISTS' || tarteenpion || ';' ;
BEGIN
   execute cmd;
END;
$$ LANGUAGE plpgsql;

--SELECT myFunction ('schema.table');
SELECT myFunction (: var1);
DROP FUNCTION myFunction (tarteenpion varchar);

My problem comes from the use of my external variable :var1 - If I directly write my value 'schema.table' with ', the function runs correctly. But I don't want that.

If I write my variable :var1 WITHOUT the ' I have this error

SELECT myFunction (: var1); => ERROR: missing FROM-clause entry for table "schema"

LINE 1: SELECT myFunction (schema.table);

So it is interpreted as not usable by the function.

If I write my variable ':var1' WITH the ' I have this error

SELECT myFunction (':var1'); => ERROR: syntax error at or near ":"

LINE 1: DROP TABLE IF EXISTS :var1

So it is not interpreted but used by the function.

Can you guide me to find a solution that has bothered me for a long time. Thank you :)

Laurenz Albe · Accepted Answer · 2020-01-31 13:36:46Z

1

You can make psql quote the variable value with single quotes:

SELECT myFunction (:'var1');

That will be replaced to

SELECT myFunction ('schema.table');

answered Jan 31, 2020 at 13:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

IFE Over a year ago

You are my hero!! It works perfectly. I never thought of that. I will try to put it all in place in my project :) Thank you so much!!

Collectives™ on Stack Overflow

Using a variable in a function with sql and sh 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