agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUse a TEXT string which is an output from a function for executing a new query in postgres
3+ messages / 3 participants
[nested] [flat]
* Use a TEXT string which is an output from a function for executing a new query in postgres
@ 2015-01-07 18:12 Roy Blum <blumroy@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Roy Blum @ 2015-01-07 18:12 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Use a TEXT string which is an output from a function for executing a new query in postgres
@ 2015-01-07 18:16 Rob Sargent <robjsargent@gmail.com>
parent: Roy Blum <blumroy@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rob Sargent @ 2015-01-07 18:16 UTC (permalink / raw)
To: pgsql-sql
On 01/07/2015 11: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:
>
> |CREATE OR REPLACEFUNCTION myresult(mytable text, myprefix text)
> RETURNS textAS
> $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_nameLIKE 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');|
> --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 stmt1as SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27acFROM 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 stmt1as THE_OUTPUT_OF_myresult();
> execute stmt1;|
>
> Thanks a lot!
> Roy
Have you looked into "dynamic sql", in which you concoct a statement and
the PERFORM it directly from you "myresult" function?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Use a TEXT string which is an output from a function for executing a new query in postgres
@ 2015-01-08 00:08 Craig Ringer <craig@2ndquadrant.com>
parent: Roy Blum <blumroy@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Craig Ringer @ 2015-01-08 00:08 UTC (permalink / raw)
To: Roy Blum <blumroy@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-01-08 00:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-01-07 18:12 Use a TEXT string which is an output from a function for executing a new query in postgres Roy Blum <blumroy@gmail.com>
2015-01-07 18:16 ` Rob Sargent <robjsargent@gmail.com>
2015-01-08 00:08 ` Craig Ringer <craig@2ndquadrant.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox