在PostgreSQL函数中如何返回临时表结果

Question

我在一个函数中使用了临时表来保存一些结果，但是我不知道如何从函数中返回表格。理想情况下，我希望可以在一个查询中完成所有操作（即不需要两个查询：一个用于调用函数，另一个用于从临时表中获取数据）。

目前我的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()中创建的，所以它不能成为其返回类型。

有什么好的想法可以解决这个问题吗？

谢谢

- Shoaib Ijaz

Answer 1

在您的 main_function() 中：

RETURN QUERY SELECT * FROM temp_t;

如果temp_t表包含列1（整数类型）、列2（布尔类型）和列3（varchar（100）类型），您还应该将返回的类型定义为：

CREATE OR REPLACE FUNCTION main_function(column1 OUT integer, column2 OUT boolean, column3 OUT varchar(100)) RETURNS SETOF record AS
(...)

另一种方法是定义新的数据类型：

CREATE TYPE temp_t_type AS (
    column1 integer,
    column2 boolean,
    column3 varchar(100)
);

这种类型可以与普通数据类型一样通过您的函数返回：

CREATE OR REPLACE FUNCTION main_function() RETURNS SETOF temp_t_type AS
(...)

...并以与上述相同的方式从函数中返回结果。

- cathulhu

Answer 2

你确定需要使用临时表吗？大多数情况下，有更便宜的解决方案。你的示例可以简单地写成：

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;

在简单情况下，您甚至不需要使用EXECUTE或者plpgsql：

CREATE OR REPLACE FUNCTION main_function()
  RETURNS SETOF tbl_t AS
$BODY$

SELECT * FROM tbl_t LIMIT 10;

$BODY$
LANGUAGE sql;

不要引用编程语言的名称，因为它是一个标识符。

- Erwin Brandstetter

Answer 3

代替

创建或替换函数main_function()返回void类型

使用类似于 CREATE OR REPLACE FUNCTION main_function() RETURNS TABLE(f1 int, f2 text) AS...

- user1575120