Thread: Use a TEXT string which is an output from a function for executing a new query in postgres
Use a TEXT string which is an output from a function for executing a new query in postgres
From
Roy Blum
Date:
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:
--now call the function:
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:
Thanks a lot!
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 function
select myresult('dkj_p_k27ac','enri');
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).
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;
Roy
Re: Use a TEXT string which is an output from a function for executing a new query in postgres
From
Rob Sargent
Date:
On 01/07/2015 11:12 AM, Roy Blum wrote:
Have you looked into "dynamic sql", in which you concoct a statement and the PERFORM it directly from you "myresult" function?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:--now call the function: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 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 myresult
('dkj_p_k27ac','enri');
"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
Re: Use a TEXT string which is an output from a function for executing a new query in postgres
From
Craig Ringer
Date:
On 01/08/2015 02:12 AM, Roy Blum wrote: > 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: This is reposted from Stack Overflow, where it has already been pretty comprehensively answered. http://stackoverflow.com/q/27808534/398670 http://stackoverflow.com/q/27824725/398670 As suggested earlier - return a refcursor. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services