My SQL script contains the following:
\set test 'some value'
DO $$DECLARE
v_test text:= :'test';
BEGIN
RAISE NOTICE 'test var is %',v_test;
END$$;
I get a syntax error when trying to evaluate the value of test:
ERROR: syntax error at or near ":"
Ideally I'd like to have an anonymous plpqsql block living in a file which will then get called from a shell script using a set of environment variables
The explanation is, according to the manual:
Variable interpolation will not be performed within quoted SQL literals and identifiers.
The body of the DO statement is a (dollar-quoted) string. So no interpolation inside the string.
Since it must be a literal string, you can also not concatenate strings on the fly. The manual:
This must be specified as a string literal, just as in
CREATE FUNCTION.
But you can concatenate the string and then execute it.
\set [ name [ value [ ... ] ] ]
Sets the psql variable name to value, or if more than one value is given, to the concatenation of all of them.
Bold emphasis mine. You just have to get the quoting right:
test=# \set test 'some value'
test=# \set code 'DECLARE v_test text := ' :'test' '; BEGIN RAISE NOTICE ''test var is: %'', v_test; END'
test=# DO :'code';
NOTICE: test var is: some value
DO
test=#
But I would rather create a (temporary) function and pass the value as parameter (where psql interpolation works). Details in this related answer on dba.SE: