Postgresql function return table? with different number of columns each time

Question

I have a table called all_checks(code integer,sql_state text) that have some checks on my db.

My table all_checks is like:

code	sql_state
100	select kind,discs,bands from music where kind='rock'
200	select name,surname,type,period from singers where period>1970
....	...
3500	select type,period from concerts

I need to create a function that runs each sql_state from all_checks according to code.

I wrote a function called run_checks(thecode integer) like this:

CREATE OR REPLACE FUNCTION run_checks(thecode integer)
  RETURNS SETOF record AS
$$

declare 
    rec record;
    sql_query character varying;
BEGIN

select distinct sql_state 
from all_checks 
where code=thecode
into sql_query;



RAISE NOTICE 'executing: %',sql_query;
for rec in EXECUTE sql_query loop
  return next rec;
end loop;
RETURN;
END
$$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION run_checks(integer)

but when I run for ex select all_checks(200) it gives this error:

ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function run_checks(integer) line 18 at RETURN NEXT

I made some search about it and I think the problem is on SETOF record but since I don't know how many columns returns each sql_state, I am confused of what return type my function should have.

Postgresql function return table? with different number of columns each time

0 Answers0