From: | Ioana Danes <ioanasoftware(at)yahoo(dot)ca> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function use in query |
Date: | 2013-06-06 13:32:04 |
Message-ID: | 1370525524.12482.YahooMailNeo@web164606.mail.gq1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ioana Danes wrote
> Hi All,
> Is there any similar syntax that only invokes the procedure once and
> returns all the columns?
Generic, adapt to fit your needs.
WITH func_call AS (
SELECT function_call(...) AS func_out_col
)
SELECT (func_out_col).*
FROM func_call;
Basically you have to execute the function call and leave the result as a
single column (a row type). Then, in another layer of the query, you expand
that single column into its components using "*". Because you are expanding
a column and not a table you must put the column name in "()" - otherwise
the parser thinks "func_out_col" is a table and errors out.
This all definitely applies to 9.2 and earlier. 9.3 (with lateral) may
behave differently...
David J.
Hi David,
Thank you for your reply, I haven't thought about it. This works as expected if I don't need to filter the table tmp_Cashdrawer:
select tmp_Cashdrawer.CashdrawerID,
(test1(tmp_Cashdrawer.CashdrawerID)).* from tmp_Cashdrawer where
tmp_Cashdrawer.CashdrawerID in (1);
If I will have to filter the tmp_Cashdrawer table then it executes the function for the all the cash drawers and then filter out the result which again is not efficient...
I might use an aggregate table for this. This way I can use a simple function call to update the aggregate table when a cash drawer is balanced or before executing the report.
Thanks again for your reply,
Ioana
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2013-06-06 14:23:45 | Re: Function use in query |
Previous Message | Shaun Thomas | 2013-06-06 13:25:37 | Re: Streaming replication with sync slave, but disconnects due to missing WAL segments |