postgresql 如何在postgresql函数中返回临时表结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17488859/
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
How to return temp table result in postgresql function
提问by Shoaib Ijaz
I am using a temporary table in a function to save some results however I don't know how to return the table from the function. Ideally I would like to do everything in one query (i.e. not two queries: one for calling the function, the other to get data from the temp table).
我在函数中使用临时表来保存一些结果，但是我不知道如何从函数返回表。理想情况下，我想在一个查询中完成所有操作（即不是两个查询：一个用于调用函数，另一个用于从临时表中获取数据）。
Currently my main_function()is as follows:
目前我的main_function()情况如下：
CREATE OR REPLACE FUNCTION main_function() RETURNS void AS
$BODY$
BEGIN
DROP TABLE IF EXISTS temp_t CASCADE;
CREATE TEMP TABLE temp_t AS SELECT * FROM tbl_t limit 0;
EXECUTE 'INSERT INTO temp_t ' || 'SELECT * FROM tbl_t limit 10';
END;
$BODY$
LANGUAGE 'plpgsql' ;
And I am calling it like so:
我这样称呼它：
SELECT * from main_function();
SELECT * from temp_t;
Again, the problem is that I don't actually want to call the second query. The first query should return the temp table as a result, however I cannot do this since the temp table is created in main_function()so it cannot be its return type.
同样，问题是我实际上不想调用第二个查询。作为结果，第一个查询应该返回临时表，但是我不能这样做，因为临时表是在其中创建的，main_function()所以它不能是它的返回类型。
Any ideas on how to achieve this?
关于如何实现这一目标的任何想法？
Thanks
谢谢
回答by cathulhu
Inside your main_function():
在你的 main_function() 里面：
RETURN QUERY SELECT * FROM temp_t;
...if temp_t table consists of e.g. column1 (type integer), column2 (boolean) and column3 (varchar(100)), you should also define returned type as:
...如果 temp_t 表由例如 column1（整数类型）、column2（布尔值）和 column3（varchar(100)）组成，您还应该将返回类型定义为：
CREATE OR REPLACE FUNCTION main_function(column1 OUT integer, column2 OUT boolean, column3 OUT varchar(100)) RETURNS SETOF record AS
(...)
Another way is to define new data type:
另一种方法是定义新的数据类型：
CREATE TYPE temp_t_type AS (
column1 integer,
column2 boolean,
column3 varchar(100)
);
That type can be returned by your functions in the same way as normal data types:
您的函数可以以与普通数据类型相同的方式返回该类型：
CREATE OR REPLACE FUNCTION main_function() RETURNS SETOF temp_t_type AS
(...)
...and return result from the function in the same way as mentioned above.
...并以与上述相同的方式从函数返回结果。
回答by Erwin Brandstetter
Are you sure you needa temporary table? Most of the time, there is a cheaper solution. Your example can simply be:
您确定需要临时表吗？大多数情况下，有更便宜的解决方案。您的示例可以简单地是：
CREATE OR REPLACE FUNCTION main_function()
RETURNS SETOF tbl_t AS
$BODY$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM tbl_t LIMIT 10';
END
$BODY$
LANGUAGE plpgsql;
You also don't need EXECUTEor even plpgsql for the simple case:
EXECUTE对于简单的情况，您也不需要甚至不需要plpgsql：
CREATE OR REPLACE FUNCTION main_function()
RETURNS SETOF tbl_t AS
$BODY$
SELECT * FROM tbl_t LIMIT 10;
$BODY$
LANGUAGE sql;
Never quote the language name. It's an identifier.
切勿引用语言名称。它是一个标识符。
回答by user1575120
instead of
代替
CREATE OR REPLACE FUNCTION main_function() RETURNS void AS
创建或替换函数 main_function() 返回 void AS
use some like CREATE OR REPLACE FUNCTION main_function() RETURNS TABLE(f1 int, f2 text) AS...
使用类似 CREATE OR REPLACE FUNCTION main_function() RETURNS TABLE(f1 int, f2 text) AS...
