Output result set in a PL/pgSQL script

Question

I have a PL/pgSQL script (not a function or procedure), which does a bunch of work. Is it possible to display the output of a SELECT statement?

DO $$
BEGIN
    SELECT * FROM my_table LIMIT 10;
END $$;

Error:

ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.

I understand that it doesnt make much sense for a procedure or function to have a select statement like this, but for a simple script I think it makes sense to be able to see the data you're working with, even if only for debugging.

Is this possible?

Answer 1

It makes a lot of sense for a function to run a query like that, and it is easy to write a function that returns the result set:

CREATE FUNCTION xyz() RETURNS SETOF my_table
   LANGUAGE plpgsql AS
$$BEGIN
   RETURN QUERY SELECT * FROM my_table LIMIT 10;
END;$$;

But a procedure or a DO statement don't return anything, so the only thing you can do is loop through the result set and send a NOTICE or INFO message to the client:

DO
$$DECLARE
   r record;
BEGIN
   FOR r IN
      SELECT * FROM my_table LIMIT 10
   LOOP
      RAISE NOTICE '%', r;
   END LOOP;
END;$$;