PostgreSQL: How to return dynamic rows from table without using "column definition list"?

Question

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

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

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

--Table

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

--Function

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;

---Function call

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

If you want dynamic columns, the short version is "you can't". The only ways to do that are with a column-definition list, or by returning some serialization of the row like a json field. — Craig Ringer, Apr 04 '14 at 14:51
Btw, what is the difference between your `if` block and your `else` block? They have exactly the same code, making the `if` condition redundant. — ADTC, Apr 15 '14 at 07:51

score 4 · Accepted Answer · answered Apr 04 '14 at 13:33

The handling of anyelement as a return type is described in Polymorphic Types:

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.

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.

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.

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.

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.

score 2 · Answer 2 · answered Apr 04 '14 at 12:54

That's because if you call your function with the value of NULL::table1 for relation_name, you must return SETOF table1.

Polymorphic arguments and results are tied to each other and are resolved to a specific data type when a query calling a polymorphic function is parsed. Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type.

http://www.postgresql.org/docs/9.3/static/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC

But you want to return with

(slno integer, fname varchar, city varchar, country varchar)

which is not row of table1 (misses the lname varchar - 3rd column).

If you are willing to call this function with only 'fname' and 'lname', your function can be much simpler:

create or replace function function1(
    column_name varchar,
    relation_name anyelement
)
returns table (
    slno integer,
    name varchar,
    city varchar,
    country varchar
)
language plpgsql as
$fun$
begin
    return query execute format(
        $sql$
            select slno, %1$I AS name, city, country
            from %2$I
            group by slno, %1$I, city, country
        $sql$,
        column_name,
        pg_typeof(relation_name)
    );
end;
$fun$;

This way, you call your function with NULL::table1 for relation_name, but you can use varchar for relation_name too, if you want (that would be more readable, like your column_name parameter).

Can you please help me for this: http://stackoverflow.com/questions/28855405/postgresql-9-3-dynamic-pivot-table-for-huge-records — MAK, Mar 05 '15 at 05:49

PostgreSQL: How to return dynamic rows from table without using "column definition list"?

2 Answers2

Linked