3

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?

3

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

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.

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:

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

  • Thanks for your answer. Unfortunately, in my case, I have to use the value from 'now_utc'. I fix the issue by using a temporary table like CREATE TEMPORARY TABLE tmp_dates (now_date timestamp); insert into tmp_dates values (:'now_utc'); but obviously your solution is better . – Łukasz Woźniczka Jun 14 at 8:56
  • @ŁukaszWoźniczka: I added one more alternative. – Erwin Brandstetter Jun 14 at 16:21
  • Thanks, @ErwinBrandstetter all of those works for me – Łukasz Woźniczka Jun 18 at 9:24
  • @ŁukaszWoźniczka: If that answers your question properly, consider accepting the answer. – Erwin Brandstetter Jun 27 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.