PostgreSQL: Structure of query does not match function result type using polymorphic type "anyelement"

Question

Problem: Getting error while returning the selected rows from table using polymorphic type "anyelement".

Error: structure of query does not match function result type.

---Table

CREATE TABLE tes1
(
    rollno integer,
    fname text,
    lname text,
    age integer,
    branch text,
    phno integer,
    email text,
    address text,
    city text,
    state text,
    country text
)

---Function

create or replace function fun_test(column_name varchar,relation_name anyelement)
returns setof anyelement as
$body$
declare
        str varchar;
        grouping varchar;
        additional_column varchar;
        select_query varchar;
begin
       if column_name='fname' then
               str:=quote_ident(column_name);
               additional_column:=' max("address") as addr, max("city") as cty, max("state") as st, max("country") as cntry';
           grouping:='"rollno"'||','||quote_ident(column_name);
   else
           str:=quote_ident(column_name);
           additional_column:=' max("address") as addr, max("city") as cty, max("state") as st, max("country") as cntry';
           grouping:='"rollno"'||','||quote_ident(column_name);
   end if;

   select_query:= 'select rollno,'||str||','||additional_column||' from '||pg_typeof(relation_name)||
   ' group by '|| grouping;

   raise info '%',select_query;

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

---Calling function

 select * from fun_test('fname',NULL::test)

Erwin Brandstetter · Accepted Answer · 2014-04-04T14:55:35.247

3

Maybe a simple typo?

CREATE TABLE tes1
...

select * from fun_test('fname',NULL::test)

As for the follow up in the comment

Error DETAIL: Returned type text does not match expected type integer in column 4

The error message is pretty obvious. You need to cast the 4th value in your SELECT statement to integer or use text in the type definition. In your current function there are more mismatches between defined type and what you return.

edited Apr 04 '14 at 14:55

answered Apr 03 '14 at 15:13

Erwin Brandstetter

605,456
145
1,078
1,228

test instead of tes1 is typing error. But error remains same. – Sarfaraz Makandar Apr 04 '14 at 05:08
Error DETAIL: Returned type text does not match expected type integer in column 4. – Sarfaraz Makandar Apr 04 '14 at 05:42
Oh! I got it. So i need to give all columns, It's not possible for displaying required columns? – Sarfaraz Makandar Apr 08 '14 at 12:48

score 0 · Answer 2 · answered Apr 03 '14 at 13:37

0

Not sure, but it seems that as you return more than one column, so the return type should be set of record.

 create or replace function fun_test(column_name varchar,relation_name anyelement)
 returns setof record as
 ...

answered Apr 03 '14 at 13:37

INait

181
6

[Explanation in the last chapter here.](http://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557) – Erwin Brandstetter Apr 03 '14 at 15:20
@INait, Yup! I tried that also. But not getting expected solution. – Sarfaraz Makandar Apr 04 '14 at 04:45
@INait, I want to call function without column definition list like as shown above in example. – Sarfaraz Makandar Apr 04 '14 at 04:52

PostgreSQL: Structure of query does not match function result type using polymorphic type "anyelement"

2 Answers2