这就是我现在正在做的,它工作正常,但必须有一个更好的方法来做到这一点.
CREATE OR REPLACE FUNCTION my_func()
RETURNS TABLE (
"a" integer,"b" varchar
) AS $$
DECLARE r record;
BEGIN
CREATE TEMP TABLE tmp_t (
"a" integer,"b" varchar
);
-- Define the tuple structure of r by SELECTing an empty row into it.
-- Is there a more straight-forward way of doing this?
SELECT * INTO r
FROM tmp_t;
-- Now I can assign values to the record.
r.a := at.something FROM "another_table" at
WHERE at.some_id = 1;
-- A related question is - how do I return the single record 'r' from
-- this function?
-- This works:
RETURN QUERY
SELECT * FROM tmp_t;
-- But this doesn't:
RETURN r;
-- ERROR: RETURN cannot have a parameter in function returning set
END; $$LANGUAGE plpgsql;
SETOF values with syntax for returning a single row or value.
— A related question is – how do I return the single record ‘r’ from
使用RETURNS TABLE声明函数时,必须在正文中使用RETURN NEXT返回行(或标量值).如果你想使用一个记录变量,它必须匹配返回类型.请进一步参考代码示例.
返回单个值或行
如果您只想返回单行,则不需要未定义类型的记录. @Kevin已经展示了两种方式.我将添加一个带OUT参数的简化版本：
CREATE OR REPLACE FUNCTION my_func(OUT a integer,OUT b text) AS $func$ BEGIN a := ...; b := ...; END $func$LANGUAGE plpgsql;
你甚至不需要添加RETURN;在函数体中,声明的OUT参数的值将在函数末尾自动返回 – 对于尚未分配的任何参数,为NULL.
而且您不需要声明RETURNS RECORD,因为OUT参数已经清楚了.
返回一组行
如果您确实想要返回多行(包括0或1行的可能性),您可以将返回类型定义为RETURNS …
> SETOF some_type,其中some_type可以是任何已注册的标量或复合类型.
> TABLE(col1 type1,col2 type2) – ad-hoc行类型定义.
> SETOF记录加上OUT参数来定义列名和类型.
100％相当于RETURNS TABLE.
> SETOF记录无需进一步定义.但是返回的行是未定义的,您需要在每次调用时都包含一个列定义列表(参见示例).
The manual about the record type：
Record variables are similar to row-type variables,but they have no
predefined structure. They take on the actual row structure of the
row they are assigned during a SELECT or FOR command.
还有更多,请阅读手册.
您可以在不指定已定义类型的情况下使用记录变量,甚至可以返回此类未定义的记录：
CREATE OR REPLACE FUNCTION my_func()
RETURNS SETOF record AS
$func$
DECLARE
r record;
BEGIN
r := (1::int,'foo'::text); RETURN NEXT r; -- works with undefined record
r := (2::int,'bar'::text); RETURN NEXT r;
END
$func$LANGUAGE plpgsql;
呼叫：
SELECT * FROM my_func() AS x(a int,b text);
但这非常难以处理,因为您必须为每次调用提供列定义列表.它通常可以用更优雅的东西代替：
>如果您在创建函数时知道类型,请立即声明它(RETURNS TABLE或朋友).
CREATE OR REPLACE FUNCTION my_func()
RETURNS SETOF tbl_or_type AS
$func$
DECLARE
r tbl_or_type;
BEGIN
SELECT INTO tbl_or_type * FROM tbl WHERE id = 10;
RETURN NEXT r; -- type matches
SELECT INTO tbl_or_type * FROM tbl WHERE id = 12;
RETURN NEXT r;
-- Or simpler:
RETURN QUERY
SELECT * FROM tbl WHERE id = 14;
END
$func$LANGUAGE plpgsql;
>如果你知道函数调用时的类型,使用polymorphic types有更优雅的方法：
Refactor a PL/pgSQL function to return the output of various SELECT queries
你的问题不清楚你究竟需要什么.
