在PL/pgSQL中声明记录的元组结构 [英] Declaring the tuple structure of a record in PL/pgSQL

问题描述

我无法在PostgreSQL文档中找到任何内容来说明如何在同时声明元组结构的同时声明记录或行.如果未定义元组结构，则会出现错误尚未分配的记录的元组结构不确定".

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".

这就是我现在正在做的事情，效果很好，但是必须有更好的方法来实现.

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;

推荐答案

您正在混合

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

-一个相关的问题是-我如何从中返回单个记录'r'

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

使用RETURNS TABLE声明函数时，必须在主体中使用RETURN NEXT返回一行(或标量值).而且，如果要使用record变量，并且该变量必须匹配，则返回类型.请参考下面的代码示例.

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

如果您只想返回一行，则不需要记录未定义的类型. @Kevin已经演示了两种方法.我将添加带有OUT参数的简化版本:

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

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参数中已经很清楚了.

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

如果您实际上想返回多个行(包括可能的0或1行)，则可以将返回类型定义为RETURNS ...

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

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

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

TABLE (col1 type1, col2 type2)-临时行类型定义.

SETOF record加上OUT参数来定义列名称和类型.
100％等同于RETURNS TABLE.

SETOF record plus OUT parameters to define column names andtypes.
100% equivalent to RETURNS TABLE.

SETOF record，无需进一步定义.但是返回的行是 undefined ，您需要在每次调用时都包括一个列定义列表(请参见示例).

SETOF record without further definition. But then the returned rows are undefined and you need to include a column definition list with every call (see example).

有关记录类型的手册:

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

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.

还有更多阅读手册.

您可以使用记录变量而无需分配定义的类型，您可以甚至返回此类未定义的记录:

You can use a record variable without assigning a defined type, you can even 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;

致电:

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

但这非常笨拙，因为您必须在每次调用时提供列定义列表.通常可以用更精美的东西代替它:

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

• 如果在创建函数时知道类型，请立即声明它(RETURNS TABLE或朋友).

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

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;

• 如果在函数调用时知道类型 ，则可以使用重构a PL/pgSQL函数可返回各种SELECT查询的输出
• 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

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

Your question is unclear as to what you need exactly.

这篇关于在PL/pgSQL中声明记录的元组结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！