How to pass variable to PL/pgSQL code from the command line?

Question

I am running a psql script from the command line with variables something like:

psql ...... -v now_utc=$NOW_UTC 

Then I want to use this in my script like:

$$
DECLARE
   _now_date timestamp := :now_utc;
BEGIN
  -- do something
END
$$

But I got an error like:

syntax error at or near ':'

The script is fine once I change from :now_utc to now() and it works like a charm.

The question is how I can pass a variable from the command line to PL/pgSQL code?

Answer 1

The manual for psql:

Variable interpolation will not be performed within quoted SQL literals and identifiers.

The body of a DO statement (or function) is a quoted literal - dollar-quoted in this case, but all the same:

$$
DECLARE
   _now_date timestamp := :now_utc;
BEGIN
   -- do something
END
$$

• Insert text with single quotes in PostgreSQL

To enable SQL interpolation, do it outside the string and then concatenate - which gets tedious quickly ...

One way to make it simpler is to let Postgres do the string processing and then execute the result using \gexec. Call from psql:

SELECT format($$
DO
$do$
DECLARE
   _now_date timestamp := %L;
BEGIN
  RAISE NOTICE '%%', _now_date;
END
$do$;
$$, :'now_utc')\gexec

NOTICE:  2019-06-14 00:41:53.040879
DO

(% has a special meaning inside format(), using %% to get a single % in the string.)

Also note the syntax :'now_utc' to get a quoted string.

• How can I quote a named argument passed in to psql?
• How to concatenate psql variables?

To just use the current UTC timestamp, you don't need all this complication:

DO
$do$
DECLARE
   _now_date timestamp := now() AT TIME ZONE 'UTC';
BEGIN
  RAISE NOTICE '%', _now_date;
END
$do$;

DO statements are not meant to take parameters. It's simpler to create a (temporary) function and pass value(s) as function parameter(s) in the call:

CREATE FUNCTION pg_temp.foo(_now_date timestamp)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
  RAISE NOTICE '%', _now_date;
END
$func$;

Call with SQL interpolation in psql:

SELECT pg_temp.foo(:'now_utc');

Finally, for passing values, you can also (ab)use a Postgres "customized option" (as session variable):

SET myvars.now_date TO :'now_utc';

DO
$do$
BEGIN
  RAISE NOTICE '%', current_setting('myvars.now_date', true);
END
$do$;

Note that the the value is stored as text and you may need to cast.

Details:

• Declaring variable for a whole script

But then you might skip the psql variable and set the option in Postgres directly ...