PostgreSQL:如何在不使用“列定义列表"的情况下从表返回动态行? [英] PostgreSQL: How to return dynamic rows from table without using "column definition list"?

问题描述

如何在不使用列定义列表"的情况下从表中动态检索行?

How to retrieve rows from table dynamically without using "column definition list"?

我正在尝试通过使用多态类型"anyelement"(伪类型)来做同样的事情，但是出现错误查询的结构与函数结果类型不匹配".

I am trying to do same by using polymorphic type "anyelement"(pseudo type) but getting error "structure of query does not match function result type".

例如:我有一个名为"table1"的表，其中包含以下详细信息.

For example: I have table called "table1" which consist of following details.

-表

create table table1
( 
  slno integer,
  fname varchar,
  lname varchar,
  city varchar,
  country varchar
)

-功能

create or replace function function1(column_name varchar,relation_name anyelement)
returns setof anyelement as
$fun$
declare 
       cname varchar; 
       add_column varchar;
       group_column varchar;
       select_query varchar;
begin
       if column_name='fname' then
          cname:=quote_ident(column_name);
          add_column:='"city"'||','||'"country"';
          group_column:='"slno"'||','||cname||','||'"city"'||','||'"country"';

       else 
          cname:=quote_ident(column_name);
          add_column:='"city"'||','||'"country"';
          group_column:='"slno"'||','||cname||','||'"city"'||','||'"country"';

       end if;

       select_query:='select slno,'||cname||','||add_column||' from '||pg_typeof(relation_name) || 'group by '||group_column;

       return query execute select_query;
end;
$fun$
language plpgsql;

---函数调用

select * from function1('fname',NULL::table1);

推荐答案

在

当函数的返回值声明为多态类型时， 必须至少有一个也是多态的自变量位置， 而作为参数提供的实际数据类型决定了 该调用的实际结果类型.

When the return value of a function is declared as a polymorphic type, there must be at least one argument position that is also polymorphic, and the actual data type supplied as the argument determines the actual result type for that call.

在您的情况下，此参数为relation_name，键入为anyelement，并且通过传递NULL::table1，这确实告诉计划者该function1的特定调用应返回SETOF table1.到目前为止一切顺利.

This argument in your case is relation_name typed as anyelement, and by passing NULL::table1, this indeed tells the planner that this particular call of function1 should return SETOF table1. So far so good.

现在的问题是，一旦执行，该函数不会返回SETOF table1，而是返回其他内容.这不是执行者所期望的，因此是错误.

Now the problem is that once executing, the function does not return SETOF table1 but something else. This is not what the executor was expecting, hence the error.

尽管问题的标题是如何返回动态行... ，但您似乎想要的是动态列或多态结果集.

Despite the title of the question being How to return dynamic rows..., what you seem to want is dynamic columns or polymorphic result sets.

这是与SQL的艰巨战斗，因为为了建立查询的执行计划，计划者必须为每个中间结果知道每一列及其类型.如果您在查询中设计的函数必须执行才能找到其输出的结构，这就会产生鸡与蛋的问题:计划必须先于执行，而不能依赖于它.

And this is an uphill battle with SQL, because in order to build the execution plan of a query, the planner has to know each column with its type for each intermediate result. If you design your query with a function that has to be executed in order to find the structure of its output, that creates a chicken and egg problem: planning must precede execution, it cannot depend on it.

通过将动态类型推断技术应用于anyelement，PostgreSQL在这个约束条件下已经在推动信封以实现尽可能多的多态性.

With its dynamic type-infering technique applied to anyelement, PostgreSQL is already pushing the envelope to implement as much polymorphism as possible given this constraint.

这篇关于PostgreSQL:如何在不使用“列定义列表"的情况下从表返回动态行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！