根据plpgsql中传递的给定表动态定义返回的行类型? [英] Dynamically define returning row types based on a passed given table in plpgsql?

问题描述

我正在plpgsql函数中动态构建查询，该函数接受源表作为传入变量.我想返回生成的SELECT语句的结果，该语句在给定的表上执行聚合，并返回该表的结果.

I'm dynamically building a query in a plpgsql function that accepts a source table as an incoming variable. I want to return the results of the built SELECT statement, which performs aggregations on the given table, and returns results of that table.

但是，此刻我出现以下错误:

However, at the moment I'm getting the following error:

**********错误**********

********** Error **********

错误:函数返回需要列定义列表 记录" SQL状态:42601

ERROR: a column definition list is required for functions returning "record" SQL state: 42601

因此，看起来我需要定义要返回的记录行的列类型.

So it looks like I need to define column types of the record rows I want to return.

我找到了这个答案，您可以通过提供结果行将与之匹配的表来绕过表类型声明.

I found this answer where you can bypass table type declaration by providing the table to which the resulting row will match.

RETURNS SETOF some_existing_table

但是，在我的情况下，由于要将要匹配其列类型的表传递给该函数，因此直到我可以操纵传递的变量(在DECLARE/BEGIN之后)，我才知道该表.

However, in my case, since the table whose column types I want to match is passed to the function, I don't know the table until I can manipulate the variables passed (after DECLARE/BEGIN).

理想情况下，我想返回我在函数内构建的聚合查询的查询结果(多行).有没有一种方法可以在函数内动态地基于已知表定义结果行返回类型?

Ideally, I want to return the query results (multiple rows) of the aggregation query I build within the function. Is there a way to define result row return types based on a known table dynamically within the function?

推荐答案

是否有一种方法可以在函数内基于已知表动态定义结果行返回类型?

Is there a way to define result row return types based on a known table dynamically within the function?

如果基于已知表"的意思是完全类似于已知表"，则是.

If by "based on a known table" you mean "exactly like a known table", then yes.

SQL是严格类型化的语言，必须使用定义明确的返回类型来创建函数.您可以像使用RETURNS SETOF record一样明显地回到匿名记录，但是随后您需要为每个调用添加列定义列表，就像错误消息告诉您的那样.像这样:

SQL is a strictly typed language and functions must be created with a well defined return type. You can fall back to anonymous records like you obviously did (with RETURNS SETOF record), but then you are required to add a column definition list for every call, like the error message tells you. Something like:

SELECT *
FROM   my_function('foo') AS foo (
          colum_name1 integer  -- name and data type for every column
        , colum_name2 text
        , colum_name3 real);

这几乎不是动态的.

您的问题尚有解释的余地​​，但是基于已知表" 将表明多态函数可以解决问题.返回类型可以动态地基于任何已注册的行类型，并且系统中的每个表都会自动返回一个类型.准系统代码示例:

Your question leaves room for interpretation, but "based on a known table" would indicate that a polymorphic function might do the trick. The return type can be based on any registered row type dynamically, and there is one for every table in the system automatically. Barebone code example:

CREATE OR REPLACE FUNCTION my_function(_rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
     'SELECT * FROM %s LIMIT 10'
    , pg_typeof(_rowtype)  -- pg_typeof() returns regtype, quoted where necessary
      );
END
$func$ LANGUAGE plpgsql;

致电:

SELECT * FROM my_function(NULL::my_table);

此相关答案中的详细说明(请参阅最后一章"各种完整表类型" ):

Detailed instructions in this related answer (look to the last chapter "Various complete table types"):

• 查看全文