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
  --
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 AS
$func$
BEGIN
  RAISE NOTICE '%', _now_date;
END
$func$  LANGUAGE plpgsql;


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