使用临时表创建函数使用这些临时表返回选择查询
我需要创建一个函数,该函数返回SELECT查询的结果。此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
我提前道歉是否有明显的错误,我这是新的。
PSQL版本的PostgreSQL 8.2.15(Greenplum数据4.3.12.0构建1)
最可能的错误,这可能在Postgres的提高:
ERROR: column "foo" specified more than once
含义,至少有一个包含在两个表中的更多列名称(id
与USING
子句折叠为一个实例)。这不会在容忍重复输出列名的普通SQL SELECT
中引发异常。但是你不能创建一个重名的表。
的问题也适用于Greenplum的(就像你以后申报),这是不 Postgres的。它在2005年从PostgreSQL中分离出来并分别开发。目前的Postgres手册几乎不适用。看看Greenplum documentation。
而psql只是标准的PostgreSQL交互式终端程序。显然你使用PostgreSQL 8.2.15附带的那个,但是RDBMS仍然是Greenplum,而不是Postgres。
语法修复(Postgres的,就像你第一次标记,还是相关的):
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的和的Greenplum:
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中,有些年份之后是Greenplum的分支。或许可以解释你的错误信息:
ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY" Position: 237
旁白:LIMIT
没有ORDER BY
产生任意性的结果。我假定你知道这一点。
如果由于某种原因,你真正需要临时表,不能升级到5.0 Greenplum的like A. Scherbaum suggested,你仍然可以使它在Greenplum的版本4.3.x工作(如在Postgres的8.2)。使用FOR
循环与RETURN NEXT
组合。
实例:
最新版本Greenplum数据(5.0)的基于PostgreSQL 8.3版本,及其支持的RETURN QUERY语法。刚刚测试您的功能:
PostgreSQL 8.4devel (Greenplum Database 5.0.0-beta.10+dev.726.gd4a707c762 build dev)
有趣。你得到的错误是什么? –
我已经将你的代码输入到SQLFiddle中,并且有一些语法技巧可能会或可能不会与你的情况相关,它似乎通过检查并返回预期结果http://sqlfiddle.com/#!15/874fa/1 –
你忘了提供你的Postgres版本,这是必不可少的。 “似乎无法让它运行”并不是我听说过的错误消息。顺便说一句,没有“psql函数”。你的是一个plpgsql函数。最后:是什么让你觉得你需要临时表?甚至一个功能? –