Use a TEXT string which is an output from a function for executing a new query in postgres

From: Roy Blum <blumroy(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Use a TEXT string which is an output from a function for executing a new query in postgres
Date: 2015-01-07 18:12:00
Message-ID: CAH4KVCCsbXffLQaznYnEKD9WTenhZtwhaE+D9YPTFNZnVDsxTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have created a function *myresult()* that receives as input Table name,
and a Prefix, it then creates an SQL one liner command to SELECT from the
specified table only the columns that share the designated prefix. It
output a string which is basically the desired SQL command. My function is
as follows and I show how I call it as well:

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
RETURNS text AS $func$DECLARE
myoneliner text;BEGIN
SELECT INTO myoneliner
'SELECT '
|| string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
|| ' FROM ' || quote_ident(mytable)
FROM information_schema.columns
WHERE table_name = mytable
AND column_name LIKE myprefix||'%'
AND table_schema = 'public'; -- schema name; might be another param

RAISE NOTICE 'My additional text: %', myoneliner;
RETURN myoneliner;END$func$ LANGUAGE plpgsql;
--now call functionselect myresult('dkj_p_k27ac','enri');

--now call the function:

select myresult('dkj_p_k27ac','enri');

And now, upon running the above procedure - I get a text string, which is
basically a query (I'll refer to it up next as 'oneliner-output', just for
simplicity). The 'oneline-output' looks as follows (i just copy/paste it
from the one output cell that i've got into here):

"SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
FROM dkj_p_k27ac"

- please note that the double quotes from both sides of the statement
were part of the myresult() output (i didn't add them by myself).

basically, I am able to copy/paste the 'oneliner-output' into a new
postgres query window and execute it as a normal query just fine -
receiving the desired columns and rows in my Data Output window. I would
like however to automate this step, so to avoid the copy/paste step. Is
there a way in postgres to use the TEXT output (the 'oneliner-output') that
I receive from myresult() function, and execute it? Can a second function
be created that would receive the output of myresult() and use it for
executing a query?

Along these lines, while I know that this scripting works and actually
output exactly the desired columns and rows:

--DEALLOCATE stmt1; -- use this line after the first time 'stmt1' was created
prepare stmt1 as SELECT
enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM
dkj_p_k27ac;execute stmt1;

I was thinking maybe something like the following scripting could work,
after doing the right tweaking?? Not sure how though..

prepare stmt1 as THE_OUTPUT_OF_myresult();execute stmt1;

Thanks a lot!
Roy

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rob Sargent 2015-01-07 18:16:54 Re: Use a TEXT string which is an output from a function for executing a new query in postgres
Previous Message David G Johnston 2015-01-07 02:21:10 Re: Count values that match elements in csv string