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);
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.
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).