Use variable set by psql meta-command inside of DO block

Answer

DO expects a string literal with plpgsql code. Symbols are not substituted inside strings in psql.
You could concatenate the whole string into a psql variable and then execute it.

Pretty multi-line format is not possible, because (per documentation):

But in any case, the arguments of a meta-command cannot continue
beyond the end of the line.

Simple example:

test=# \set value foo
test=# \set do 'BEGIN\n   RAISE NOTICE ''v: %'', ' :'value' ';\nEND'
test=# DO :'do';
NOTICE:  v: foo

Replace line breaks with \n (or remove them if you don’t care for pretty format). Based on this adapted code:

DO
'
DECLARE
   _val  text;
   _vals text[] := string_to_array(>>values<<, '','');
BEGIN
   FOREACH _val IN ARRAY _vals
   LOOP
     RAISE NOTICE ''v: %'', _val;
   END LOOP;
END
'

It looks like this:

test=# \set do 'DECLARE\n   _val  text;\n   _vals text[] := string_to_array(' :'values' ', '','');\nBEGIN\n   FOREACH _val IN ARRAY _vals\n   LOOP\n     RAISE NOTICE ''v: %'', _val;\n   END LOOP;\nEND'
test=# DO :'do';
NOTICE:  v: foo
NOTICE:  v: bar
NOTICE:  v: baz
DO

I added bold emphasis to the variable to make it easier to spot.

Related answer by @Pavel (ab)using a server session variable:

Alternative solutions

Prepared statement

Your current solution doesn’t look that bad. I would simplify:

PREPARE get_values AS SELECT * FROM regexp_split_to_table(:'values', ',');

DO
$do$
DECLARE
   _val text;
BEGIN
   FOR _val IN EXECUTE
      'EXECUTE get_values'
   LOOP
      RAISE NOTICE 'v: %', _val;
   END LOOP;
END
$do$;

Temporary table

Similar solution with a temporary table:

CREATE TEMP TABLE tmp AS SELECT * FROM regexp_split_to_table(:'values', ',') v;

DO
$do$
DECLARE
   _val text;
BEGIN
   FOR _val IN
      TABLE tmp
   LOOP
      RAISE NOTICE 'v: %', _val;
   END LOOP;
END
$do$;

Leave a Comment