PostgreSQL: How to display only selected columns of a single table within CASE expression in function?

Question

Example: I am passing two parameters to function namely n(case number) and tname(table name), and want to display rows accordingly.

--Table "testing"

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

--Rows insertion

insert into testing values(1,'aaa','bbb',25,'CS',1234567890,'abc@gmail.com','sector1','xyz','zyx','yxz');

insert into testing values(2,'zzz','xxx',25,'EE',987654321,'zzz@gmail.com','sector2','uvw','wvu','vuw');

--Function "f1()"

create or replace function f1(n integer,tname varchar)/*n for case number and tname for table name */
returns setof tname as 
$body$
begin
     case n
     when 1 then
     return query execute format ($$ select rollno,fname from %I $$,tname);

     when 2 then 
     return query execute format ($$ select lname,age,branch from %I $$,tname);

     when 3 then
     return query execute format ($$ select phno,email,address,city,country from %I $$,tname);

     end case;
end
$body$
language plpgsql;

--Function calling

select * from f1(1,'testing');
/*Show only case "1" select query result*/

select * from f1(2,'testing');
/*Show only case "2" select query result*/

select * from f1(3,'testing');
/*Show only case "3" select query result*/

Function return types cannot be dynamic based on a parameter. You'd have to use `RETURNS SETOF RECORD` - in which case the caller has to specify the expected data type(s). — Craig Ringer, Apr 01 '14 at 07:13
@Craig Ringer, So which block of code do i specify the columns definitions? — Sarfaraz Makandar, Apr 01 '14 at 07:19
the caller of the function. See http://www.postgresql.org/docs/current/static/sql-select.html, search for "column definition list". — Craig Ringer, Apr 01 '14 at 07:26

score 2 · Accepted Answer · edited May 23 '17 at 12:01

While Craig is correct that return types cannot be dynamic in function declarations, there is a way around this with polymorphic types. This is surprisingly simple and would actually work flawlessly:

CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM '|| pg_typeof(_tbl_type);
END
$func$ LANGUAGE plpgsql;

Call (important!):

SELECT rollno,fname FROM data_of(NULL::testing);
SELECT * FROM data_of(NULL::my_schema.my_table);
SELECT * FROM data_of(NULL::my_custom_type);

What you need is a well-known type. For every table there is a well-known type automatically. But you can create any type, cast NULL to it and pass it to the function. This way you can build exactly what you have in your question ...

Related answer with a lot more details:
Refactor a PL/pgSQL function to return the output of various SELECT queries

PostgreSQL: How to display only selected columns of a single table within CASE expression in function?

1 Answers1