Raise notice to print a table's data

Question

I'd like to be able to print some debug information from sql script / function. Of course, I can do this by

RAISE NOTICE 'hello!'

But I also need to print a whole table's data. This doesn't work:

RAISE NOTICE '%' (SELECT * FROM table1)

Is it possible and how?

Madlin

Answer 1

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: %, col2: %', quote_ident(items.col1), quote_ident(items.col2);
END LOOP;

where items is declared as RECORD.

Fluff

loop variable of loop over rows must be a record or row variable or list of scalar variables – Madlin

Yes, I mentioned that items should be declared as RECORD above. – Fluff

Answer 2

Since postgres 9.3 you can use to_json() to convert record into text suitable for notice,

RAISE NOTICE '%', to_json(record1);

Eveliaevelin

For entire table, you will want something along the lines of SELECT array_to_json(array_agg(t)) FROM t. – Meijer

I tried that, and I'm getting error: ERROR: syntax error at or near "to_json" – Scoop

pg 9.3 is required – Eveliaevelin

You're missing a comma, this should be RAISE NOTICE '%', to_json(record1); – Boudreau

Answer 3

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.

Dikmen