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

我在Postgresql文档中找不到任何显示如何在同时声明元组结构时声明记录或行的内容.如果您没有定义元组结构,则会收到错误“尚未分配的记录的元组结构是不确定的”.

这就是我现在正在做的,它工作正常,但必须有一个更好的方法来做到这一点.

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

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

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

猜你在找的Postgre SQL相关文章