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

Declaring the tuple structure of a record in PL/pgSQL

提问by nnyby

I can't find anything in the PostgreSQL documentation that shows how to declare a record, or row, while declaring the tuple structure at the same time. If you don't define you tuple structure you get the error "The tuple structure of a not-yet-assigned record is indeterminate".

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

This is what I'm doing now, which works fine, but there must be a better way to do it.

这就是我现在正在做的，效果很好，但必须有更好的方法来做到这一点。

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;

回答by Erwin Brandstetter

You are mixing the syntax for returning SETOFvalues with syntax for returning a single row or value.

您将返回SETOF值的语法与返回单行或值的语法混合在一起。

-- A related question is - how do I return the single record 'r' from

- 一个相关的问题是 - 我如何从

When you declare a function with RETURNS TABLE, you have to use RETURN NEXTin the body to return a row (or scalar value). And if you want to use a recordvariable with that it has to matchthe return type. Refer to the code examples further down.

当您使用 声明函数时RETURNS TABLE，您必须RETURN NEXT在主体中使用以返回行（或标量值）。如果你想使用一个record变量，它必须匹配返回类型。请参阅下面的代码示例。

Return a single value or row

返回单个值或行

If you just want to return a single row, there is no needfor a record of undefined type. @Kevin already demonstrated two ways. I'll add a simplified version with OUTparameters:

如果只想返回单行，则不需要未定义类型的记录。@Kevin 已经展示了两种方式。我将添加一个带有OUT参数的简化版本：

CREATE OR REPLACE FUNCTION my_func(OUT a integer, OUT b text)
   AS
$func$
BEGIN
   a := ...;
   b := ...;
END
$func$ LANGUAGE plpgsql;

You don't even need to add RETURN;in the function body, the value of the declared OUTparameters will be returned automatically at the end of the function - NULLfor any parameter that has not been assigned.
And you don't need to declare RETURNS RECORDbecause that's already clear from the OUTparameters.

您甚至不需要RETURN;在函数体中添加，声明OUT参数的值将在函数结束时自动返回 -NULL对于任何尚未分配的参数。
而且您不需要声明，RETURNS RECORD因为从OUT参数中已经很清楚了。

Return a set of rows

返回一组行

If you actually want to return multiplerows (including the possibility for 0 or 1 row), you can define the return type as RETURNS...

如果您确实想返回多行（包括 0 或 1 行的可能性），您可以将返回类型定义为RETURNS...

• SETOF some_type, where some_typecan be any registered scalar or composite type.

• TABLE (col1 type1, col2 type2)- an ad-hoc row type definition.

• SETOF recordplus OUTparameters to define column names andtypes.
  100% equivalent to RETURNS TABLE.

• SETOF recordwithout further definition. But then the returned rows are undefinedand you need to include a column definition list with every call (see example).

• SETOF some_type, 其中some_type可以是任何注册的标量或复合类型。

• TABLE (col1 type1, col2 type2)- 一个特别的行类型定义。

• SETOF record加上OUT参数来定义列名和类型。
  100% 相当于RETURNS TABLE.

• SETOF record没有进一步的定义。但是返回的行是未定义的，您需要在每次调用时都包含一个列定义列表（参见示例）。

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.

记录变量类似于行类型变量，但它们没有预定义的结构。它们采用在 SELECT 或 FOR 命令期间分配给它们的行的实际行结构。

There is more, readthe manual.

还有更多，请阅读手册。

You canuse a record variable without assigning a defined type, you caneven return such undefined records:

您可以在不分配定义类型的情况下使用记录变量，您甚至可以返回此类未定义的记录：

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;

Call:

称呼：

SELECT * FROM my_func() AS x(a int, b text);

But this is very unwieldyas you have to provide the column definition list with every call. It can generally be replaced with something more elegant:

但这非常笨拙，因为您必须在每次调用时都提供列定义列表。它通常可以用更优雅的东西代替：

• If you know the type at time of function creation, declare it right away (RETURNS TABLEor friends).

• 如果您在创建函数时知道类型，请立即声明（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;

• If you know the type at time of the function call, there are more elegant ways using polymorphic types:
  Refactor a PL/pgSQL function to return the output of various SELECT queries

• 如果您知道函数调用时的类型，还有更优雅的使用多态类型的方法：
  重构 PL/pgSQL 函数以返回各种 SELECT 查询的输出

Your question is unclear as to what you need exactly.

您的问题不清楚您到底需要什么。

回答by kgrittn

There might be some way that avoids the explicit type declaration, but offhand the best I can come up with is:

可能有一些方法可以避免显式类型声明，但我能想到的最好的方法是：

CREATE TYPE my_func_return AS (
    a integer,
    b varchar
  );

CREATE OR REPLACE FUNCTION my_func()
  RETURNS my_func_return AS $$
DECLARE
  r my_func_return;
BEGIN
  SELECT 1, 'one' INTO r.a, r.b;
  RETURN r;
END; $$ LANGUAGE plpgsql;

Oh, I almost forgot the simplest way to do this:

哦，我差点忘了最简单的方法：

CREATE OR REPLACE FUNCTION my_func2(out a int, out b text)
  RETURNS RECORD AS $$
BEGIN
  SELECT 1, 'one' INTO a, b;
  RETURN;
END; $$ LANGUAGE plpgsql;

回答by Craig Ringer

It is much easier to use OUTparameters rather than a record. If iteratively building a set of records (a table) use RETURN NEXT. If generating from a query, use RETURN QUERY. See:

使用OUT参数比使用记录要容易得多。如果迭代地构建一组记录（一个表），请使用RETURN NEXT. 如果从查询生成，请使用RETURN QUERY. 看：

https://stackoverflow.com/a/955289/398670

https://stackoverflow.com/a/955289/398670

and:

和：

http://www.postgresql.org/docs/current/static/plpgsql-declarations.htmlhttp://www.postgresql.org/docs/current/static/sql-createfunction.htmlhttp://www.postgresonline.com/journal/archives/129-Use-of-OUT-and-INOUT-Parameters.html

http://www.postgresql.org/docs/current/static/plpgsql-declarations.html http://www.postgresql.org/docs/current/static/sql-createfunction.html http://www.postgresonline。 com/journal/archives/129-Use-of-OUT-and-INOUT-Parameters.html

Think:

思考：

CREATE OR REPLACE FUNCTION my_func(OUT a integer, OUT b varchar) RETURNS SETOF RECORD AS $$
BEGIN
    -- Assign a and b, RETURN NEXT, repeat. when done, RETURN.
END;
$$ LANGUAGE 'plpgsql';