问题描述:

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,'[email protected]','sector1','xyz','zyx','yxz');

insert into testing values(2,'zzz','xxx',25,'EE',987654321,'[email protected]','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*/

网友答案:

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

相关阅读:
Top