postgresql – 在PL/pgSQL中声明记录的元组结构

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;

你正在混合 syntax for returning 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

你的问题不清楚你究竟需要什么.