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?

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

where items is declared as RECORD.

Answer 2

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

RAISE NOTICE '%', to_json(record1);