ppgsql declaring variable from parameter

Question

I am running a sql query from the command line where I pass in value called tablename:

psql "hostname" -v tablename=$1 -a -q -f "filename.sql"

I then set the variable like this:

\set tablename :tablename

And then I have a function where I want to use the variable tablename like this:

DO $$
BEGIN
    
    if (tablename = 'movie_table') then
     -- query goes in here 
    END if;
END;
$$;

I have also tried declaring the variable with a few variations of this:

DECLARE tablename varchar := :tablename;

Please let me know if you have any suggestions. I get the following error:

syntax error at or near ":"

Pavel Stehule · Accepted Answer · 2020-11-23 19:32:08Z

Your code cannot to work, because you try to read client side variables (psql) on server side (plpgsql). You need to push the value to custom server side variable, and in next step you can read it from plpgsql environment:

postgres=# \set client_side_var Ahoj
postgres=# select set_config('my.client_side_var', :'client_side_var', false);
┌────────────┐
│ set_config │
╞════════════╡
│ Ahoj       │
└────────────┘
(1 row)

postgres=# do $$
declare var text default current_setting('my.client_side_var');
begin
   raise notice '>>>%<<<', var;
end;
$$;
NOTICE:  >>>Ahoj<<<
DO

You cannot to use a psql syntax :var inside plpgsql code.

Collectives™ on Stack Overflow

ppgsql declaring variable from parameter

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related