postgresql 发出通知以打印表的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23729752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Raise notice to print a table's data
提问by アレックス
I'd like to be able to print some debug information from sql script / function. Of course, I can do this by
我希望能够从 sql 脚本/函数打印一些调试信息。当然，我可以通过
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?
是否可能以及如何实现？
回答by khampson
The most straightforward way would be to iterate over the rows in a for loopand use RAISE NOTICEcontaining each column you're interested in interpolated in it.
最直接的方法是迭代 a 中的行for loop并使用RAISE NOTICE包含您感兴趣的每一列插入其中。
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.
其中 items 声明为RECORD.
回答by Сухой27
Since postgres 9.3 you can use to_json()to convert record into text suitable for notice,
从 postgres 9.3 开始，您可以使用to_json()将记录转换为适合通知的文本，
RAISE NOTICE '%', to_json(record1);
回答by Evgeny Nozdrev
RAISE NOTICEwill print table data without alignment, so it will be hard to read. More flexible way is to use refcursor:
RAISE NOTICE将打印表格数据而不对齐，因此很难阅读。更灵活的方法是使用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.
这样的refcursor 将可用于在同一事务期间读取。如果你没有用 BEGIN 和 ROLLBACK（或 COMMIT）来包装你的测试，PostgreSQL 将无法找到这个。
