代码之家 › 专栏 › 技术社区 › postgresql_beginner

使用临时表创建函数,这些临时表使用这些临时表返回select查询

greenplum stored-functions plpgsql

postgresql_beginner · 技术社区 · 7 年前

我需要创建一个函数,返回SELECT查询的结果。这个选择查询是在这个函数中创建的几个临时表的连接。有没有办法创建这样的功能?下面是一个示例(非常简单,实际上有多个带有长查询的临时表):

CREATE OR REPLACE FUNCTION myfunction () RETURNS TABLE (column_a TEXT, column_b TEXT) AS $$
BEGIN
CREATE TEMPORARY TABLE raw_data ON COMMIT DROP
AS
SELECT d.column_a, d2.column_b FROM dummy_data d JOIN dummy_data_2 d2 using (id);

RETURN QUERY (select distinct column_a, column_b from raw_data limit 100);
END;
$$
LANGUAGE 'plpgsql' SECURITY DEFINER

我得到错误:

[Error] Script lines: 1-19 -------------------------
 ERROR: RETURN cannot have a parameter in function returning set;
 use RETURN NEXT at or near "QUERY"Position: 237

2 回复 | 直到 7 年前

A. Scherbaum 7 年前

最新版本的Greenplum数据库(5.0)基于PostgreSQL 8.3,支持返回查询语法。刚刚在以下设备上测试了您的功能:

PostgreSQL 8.4devel (Greenplum Database 5.0.0-beta.10+dev.726.gd4a707c762 build dev)

Erwin Brandstetter 7 年前

这可能在Postgres中引起的最可能的错误:

id 将其折叠为一个实例 USING 条款)包含在两个表中。这不会在普通SQL中引发异常 SELECT 允许重复的输出列名。但不能创建具有重复名称的表。

这个问题也适用于 (就像你后来宣布的那样),这是不博士后。它于2005年从PostgreSQL派生出来,并单独开发。目前的Postgres手册几乎不再适用。注意 Greenplum documentation .

和 psql

CREATE OR REPLACE FUNCTION myfunction()
  RETURNS TABLE (column_a text, column_b text) AS
$func$
BEGIN
   CREATE TEMPORARY TABLE raw_data ON COMMIT DROP AS
   SELECT d.column_a, d2.column_b  -- explicit SELECT list avoids duplicate column names
   FROM   dummy_data   d
   JOIN   dummy_data_2 d2 using (id);

   RETURN QUERY
   SELECT DISTINCT column_a, column_b
   FROM   raw_data
   LIMIT  100;
END
$func$  LANGUAGE plpgsql SECURITY DEFINER;

该示例不需要临时表-除非在同一事务中的函数调用后访问临时表( ON COMMIT DROP ). 此外,普通SQL函数在各个方面都更好。Postgres语法青梅:

CREATE OR REPLACE FUNCTION myfunction(OUT column_a text, OUT column_b text)
  RETURNS SETOF record AS
$func$
   SELECT DISTINCT d.column_a, d2.column_b
   FROM   dummy_data   d
   JOIN   dummy_data_2 d2 using (id)
   LIMIT  100;
$func$  LANGUAGE plpgsql SECURITY DEFINER;

不仅如此, it should also work for Greenplum

此功能剩下的唯一原因是 SECURITY DEFINER . 否则,您可以只使用简单的SQL语句(可能作为预处理语句)。

RETURN QUERY 在2008年(几年前)以8.3版本添加到PL/pgSQL中青李的叉子。可能会解释您的错误消息:

ERROR: RETURN cannot have a parameter in function returning set;
use RETURN NEXT at or near "QUERY" Position: 237

旁白: LIMIT 没有 ORDER BY

如果出于某种原因,您实际上需要临时表,并且无法升级到Greenplum 5.0 like A. Scherbaum suggested FOR 环路与 RETURN NEXT .
示例: