Postgres：如何在循环中打印值

本文介绍了Postgres：如何在循环中打印值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想打印从查询中获得的所有表。但是我的代码仅显示函数的名称。

I want to print all tables that I got from the query. However my code displays only the name of the function.

# create or replace function remove_scene() # RETURNS void AS $$ # DECLARE # row record; # BEGIN # FOR row IN # select table_name from information_schema.tables WHERE table_schema = 'public' AND table_name ~ 'measurement[0-9]' # LOOP # RAISE NOTICE '** %', quote_ident(row.table_name); # END LOOP; # END; # $$ LANGUAGE plpgsql; CREATE FUNCTION # select remove_scene(); remove_scene -------------- (1 row) #

推荐答案

您看不到任何内容的原因是您的函数未返回任何内容。使用 RAISE NOTICE 写入的文本仅在以下情况下显示在客户端中：a）客户端支持（psql这样做）和b）如果您的会话配置为实际归还它们。为了使您的加薪出现在 psql 中，您需要使用：

The reason you are not seeing anything is that your function doesn't return anything. Text that is "written" using RAISE NOTICE will only be displayed in the client if a) the client supports that (which psql does) and b) if your session is configured to actually return them. In order for your raise to be seen in psql you need to use:

set client_min_messages = NOTICE;

您当前的设置可能是警告，因此

Your current setting is probably WARNING and therefor a NOTICE is not displayed.

不过：用 RAISE NOTICE循环执行此操作效率很低。最好将函数更改为返回的函数集：

However: doing this in a loop with RAISE NOTICE is very inefficient. It would be much better to change your function into a set returning function:

create or replace function remove_scene() RETURNS table(table_name text) AS $$ select table_name from information_schema.tables WHERE table_schema = 'public' AND table_name ~ 'measurement[0-9]' $$ LANGUAGE sql;

然后像这样使用它：

select * from remove_scene();

另一种选择是将选择语句放入视图中。

Another option would be to put that select statement into a view.