如何在postgresql函数中返回临时表结果 [英] How to return temp table result in postgresql function

问题描述

我在函数中使用临时表来保存一些结果，但是我不知道如何从函数中返回该表.理想情况下，我想在一个查询中完成所有操作(即不是两个查询:一个用于调用函数，另一个用于从临时表中获取数据).

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).

目前我的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' ;

我是这样称呼它的:

SELECT * from main_function();
SELECT * from temp_t;

同样，问题是我实际上不想调用第二个查询.第一个查询应该返回临时表作为结果，但是我不能这样做，因为临时表是在 main_function() 中创建的，所以它不能是它的返回类型.

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.

关于如何实现这一目标的任何想法?

Any ideas on how to achieve this?

谢谢

推荐答案

在你的 main_function() 中:

Inside your main_function():

RETURN QUERY SELECT * FROM temp_t;

...如果 temp_t 表包含例如column1(整数类型)、column2(布尔值)和 column3(varchar(100))，您还应该将返回类型定义为:

...if temp_t table consists of e.g. column1 (type integer), column2 (boolean) and column3 (varchar(100)), you should also define returned type as:

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.

这篇关于如何在postgresql函数中返回临时表结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！