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

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?

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 ...

StackExchange Database Administrators Q#240513, answer score: 10