Use a variable in a postgresql execute statement

Question

I am trying to have a parameter when creating a trigger function.

I have been trying to use this code:

DO $DO$ BEGIN EXECUTE format($TRIGGER$ CREATE OR REPLACE FUNCTION my_schema.my_trigger_fcn() RETURNS trigger AS $BODY$ DECLARE my_geom geometry(MultiPoint,%1$s); BEGIN my_geom = st_collect(NEW.situation_geometry)::geometry(MultiPoint,%$1s); NEW.geometry = my_geom; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; $TRIGGER$, :SRID); END $DO$;

and trying to run this code with psql -v SRID=2056 -f myfile. But I get a syntax error.

I have also tried the SQL execute command, but prepared statements are not allowed to create trigger function.

Any idea?

SOLUTION:

Thanks to @Pavel Stehule, here is the code that works:

SELECT set_config('my.srid', :SRID::text, false); DO $DO$ BEGIN EXECUTE format($TRIGGER$ CREATE OR REPLACE FUNCTION qgep_od.my_trigger_fcn() RETURNS trigger AS $BODY$ DECLARE my_geom geometry(MultiPoint,%1$s); BEGIN my_geom = st_collect(NEW.situation_geometry)::geometry(MultiPoint,%1$s); NEW.geometry = my_geom; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; $TRIGGER$, current_setting('my.srid')); END $DO$;

Pavel Stehule · Accepted Answer · 2018-07-05 14:04:43Z

You cannot to use psql variables inside any SQL string. The string is body of DO command too. You can use session variables:

\set myvar xxx select set_config('my.myvar', :'myvar', false); do $$ begin execute format('create or replace function fx() returns void as $_$begin raise notice %L; end$_$ language plpgsql', current_setting('my.myvar')); end; $$; postgres=# \sf fx CREATE OR REPLACE FUNCTION public.fx() RETURNS void LANGUAGE plpgsql AS $function$begin raise notice 'xxx'; end$function$

Other possibility is do this replacement before psql - you can use sed

Collectives™ on Stack Overflow

Use a variable in a postgresql execute statement

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related