Use an eval function to return table

Question

I am interested in learning how to use dynamic functions in Postgres. I basically need a dynamic function that will spit out a table or a subtable (a few chosen columns from an existing table).

I've created the following eval() function, but it is not working as I'd like it to. I would like my function to return the result of a query that is introduced as a string within the function. The function currently returns only the first value from the first column (enrich_d_dkj_p_k27ac). It seems like I should change my returns from text to something else?

create or replace function eval() returns text
as
$body$
declare
  result text;
begin
  execute 'select enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
  from dkj_p_k27ac' into result;
  return result;
end;
$body$
language plpgsql;

SELECT eval();

Result is:

eval text
2.4

But this is basically only the very first value of the first column - I will need the entire table to appear - or in other words - the result of the indicated SELECT.

score 1 · Accepted Answer · edited May 23 '17 at 11:50

It would work like this (but it's useless):

create or replace function eval()
  RETURNS TABLE (enrich_d_dkj_p_k27ac text  -- replace with actual column types
               , enrich_lr_dkj_p_k27ac text
               , enrich_r_dkj_p_k27ac text) AS
$func$
begin

RETURN QUERY EXECUTE
'select enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
 from dkj_p_k27ac';

end
$func$  language plpgsql;

Call:

SELECT * FROM eval();

You do not need the eval() function, just execute the statement directly:

select enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
from dkj_p_k27ac;

The dynamic call cannot solve your underlying problem:

How to execute a string result of a stored procedure in postgres

Use an eval function to return table

1 Answers1