Raise notice to print a table's data
The most straightforward way would be to iterate over the rows in a for loop
and use RAISE NOTICE
containing each column you're interested in interpolated in it.
i.e. something like:
FOR items IN SELECT * FROM table1 LOOP
RAISE NOTICE 'col1: %s, col2: %s', quote_ident(items.col1), quote_ident(items.col2);
END LOOP;
where items is declared as RECORD
.
Since postgres 9.3 you can use to_json()
to convert record into text suitable for notice,
RAISE NOTICE '%', to_json(record1);
RAISE NOTICE
will print table data without alignment, so it will be hard to read. More flexible way is to use refcursor
:
DECLARE
_temp_cur1 refcursor = 'unique_name_of_temp_cursor_1';
...
BEGIN
...
OPEN _temp_cur1 FOR
SELECT *
FROM table1;
...
END
Then run function in transaction:
BEGIN;
SELECT my_func();
FETCH ALL FROM "unique_name_of_temp_cursor_1"; --here is double-quotes ""!
ROLLBACK; --do not save any changes to DB during tests (or use COMMIT;)
Such refcursor will be available for reading during the same transaction. If you do not wrap your test with BEGIN and ROLLBACK (or COMMIT), PostgreSQL will not be able to find this one.