How to return a value from a .sql file that uses the anonymous DO

Question

I have the following (working) code that I run from the command line:

DO $$
DECLARE first_ext INTEGER;
        last_ext INTEGER;
BEGIN
   first_ext:=100;
   last_ext:=150;

   INSERT INTO widgets (username)
   SELECT i
   FROM generate_series(first_ext, last_ext) AS t(i);

   INSERT INTO widget2
   SELECT generate_series(first_ext, last_ext), 'sometext', 
          generate_series(first_ext, last_ext);
END $$;

I'd like to now do a count on the two tables to make sure that i have correctly created data.
But I'm having a hard time returning the value. This is what I tried:

RETURNS rec_count AS
DO $$
DECLARE first_ext INTEGER;
        last_ext INTEGER;
BEGIN
   first_ext:=100;
   last_ext:=150;

   INSERT INTO widgets (username)
   SELECT i
   FROM generate_series(first_ext, last_ext) AS t(i);

   INSERT INTO widget2
   SELECT generate_series(first_ext, last_ext), 'sometext', 
          generate_series(first_ext, last_ext);

   SELECT COUNT(*) FROM widget2 INTO rec_count;
END $$;

But it fails with the following error:

ERROR:  syntax error at or near "RETURNS" LINE 1: RETURNS rec_count AS

I don't want to make this into a function / stored proc.
Is there any other way to do this?

score 1 · Accepted Answer · edited May 23 '17 at 12:16

You cannot return values from a DO statement. There are various workarounds:

Get result from query in DO satement

But why not create a temporary function?

CREATE FUNCTION pg_temp.f_dummy(OUT rec_count bigint) AS
$func$
DECLARE
   first_ext int := 100;  -- assign at declaration time
   last_ext  int := 150;
BEGIN
   INSERT INTO widgets (username)
   SELECT i
   FROM   generate_series(first_ext, last_ext) i;

   INSERT INTO widget2(col_a, col_b, col_c)  -- column names !?
   SELECT i, 'sometext', i
   FROM   generate_series(first_ext, last_ext) i;  -- do not call it twice

   SELECT COUNT(*) FROM widget2 INTO rec_count;  -- counts *all* rows in widget2
END
$func$  LANGUAGE plpgsql;

Call:

SELECT pg_temp.f_dummy();

Only visible within the current session; dies at the end of the session like all temporary objects.

How to create a temporary function in PostgreSQL?

Or just use a single SQL statement with data-modifying CTEs and the RETURNING clause:

WITH ins1 AS (
   INSERT INTO widgets (username)
   SELECT i
   FROM   generate_series(100, 150) i  --  enter number here *once*
   RETURNING username
   )
, ins2 AS (
   INSERT INTO widget2(username, txt, some_int)
   SELECT username, 'sometext', username
   FROM   ins1
   RETURNING widget2.username
   )
SELECT count(*) FROM ins2;

Only counts newly inserted rows in widget2, excluding pre-existing rows.

Aside: username is an odd column name for an integer column.

How to return a value from a .sql file that uses the anonymous DO

1 Answers1