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.

Community · Accepted Answer · 2017-05-23 11:50:56Z

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

Collectives™ on Stack Overflow

Use an eval function to return table

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related