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 general syntax of a variable declaration is: name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ]; The DEFAULT clause, if given, specifies the initial value assigned to the variable when the block is entered.
You can declare variables only in the DECLARE section of a block.
In general, yes you can, you just nest another execution block inside your current one...
In PostgreSQL, a variable is a meaningful name for a memory location. A variable holds a value that can be changed through the block or function. A variable is always associated with a particular data type. Before using a variable, you must declare it in the declaration section of the PostgreSQL Block.
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With