PostgreSQL – Assigning Query Results to Multiple Variables in Functions

This is a bad idea. You're creating a MATERIALIZED VIEW, just use the native functionality for that.

CREATE MATERIALIZED VIEW schema_test
  AS SELECT timestamp, component_name, component_type, status, message;

Then simply run REFRESH MATERIALIZED VIEWschema_test; it will clear out the old results and insert the new results of the query.

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