PostgreSQL: Displaying result using RECORD type variable in function

Question

I am passing column name and table name as parameter to function for dynamic query as shown below in example.

Problem: While executing "SELECT" query within function, it only displaying the structure of the table not rows.

Example:

--Table

create table test1
(
   rollno integer,
   fname text,
   lname text,
   age integer,
   branch text,
   phno integer,
   email text,
   address text,
   city text,
   state text,
   country text
 );

--Inserting some rows

insert into tes1 values(1,'aaa','bbb',25,'CS',1234567890,'[email protected]','sector1','xyz','zyx','yxz');

insert into tes1 values(2,'zzz','xxx',25,'EE',987654321,'[email protected]','sector2','uvw','wvu','vuw');

--Function

create or replace function fun1(colB text,vname varchar)
returns setof record as
$body$
declare 
       str text;
       grp text;
       addi text;
       sqlq varchar;
       tname varchar;
begin
       if colB='fname' then
         str:='fname';
         grp:='rollno'||','||'fname';
         addi:='city'||','||'state'||','||'country';
         tname:=vname;

       elsif colB='lname' then
         str:='lname';
         grp:='rollno'||','||'lname';
         addi:='city'||','||'state'||','||'country';
         tname:=vname;
       end if;

   raise info '%',str;
   raise info '%',grp;
   raise info '%',addi;
   raise info '%',vname;
   raise info '%',tname;

   sqlq:='select rollno,'||str||',age,branch,'||addi||' from '|| tname;
   raise info '%',sqlq;
   execute sqlq;    

end;
$body$
language plpgsql;

--Function calling

select * from fun1('lname','test1') as ("rollno" integer,"lname" text,
"age" integer,"branch" text,"city" text,"state" text,"country" text);

INFO:  lname
INFO:  rollno,lname
INFO:  city,state,country
INFO:  tes1
INFO:  tes1
INFO:  select rollno,lname,age,branch,city,state,country from tes1
rollno | lname | age | branch | city | state | country
--------+-------+-----+--------+------+-------+---------
(0 rows)

pozs · Accepted Answer · 2014-04-01 12:34:00Z

2

You must return with the results inside a function (unless it's RETURNS VOID), like

RETURN QUERY EXECUTE sqlq;

And, you should properly escape the column names, f.ex. with the format() function, like

RETURN QUERY EXECUTE format('SELECT "col1", %I FROM %I', 'col2', 'test1');

edited Apr 1, 2014 at 12:34

answered Apr 1, 2014 at 12:28

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

Sanitizing dynamic input is a must, but that only applies to the table name in this case (in the original example, the answer deviates). The double quotes around "col1" and the treatment of col2 serve no purpose here - both being hard coded, legal, lower-case names. More details.

pozs Over a year ago

@ErwinBrandstetter they are just an example, how to use format function for this purpose.

Collectives™ on Stack Overflow

PostgreSQL: Displaying result using RECORD type variable in function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related