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