pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsage of function retruning record in query
8+ messages / 4 participants
[nested] [flat]
* Usage of function retruning record in query
@ 2011-07-04 06:47 gmb <gmbouwer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: gmb @ 2011-07-04 06:47 UTC (permalink / raw)
To: pgsql-sql
Hi
I have a stored function returning a record consisting of two field, and
receiving as input a single identifier:
CREATE FUNCTION calcvalues(IN itemid VACHAR, OUT calcval1 NUMERIC, OUT
calcval2 NUMERIC)
RETURNS record...
Now, I want to use this function in a query as follows:
SELECT itemid, calcvalues(itemid) FROM itemlist;
which returns:
itemid | calcvalues
--------+-------------
4 | (0.67,10.00)
5 | (1.55,45.00)
6 | (3.60,69.00)
Now, how should I go about to give the following output:
itemid | calcval1 | calcval2
--------+----------+----------
4 | 0.67 | 10.00
5 | 1.55 | 45.00
6 | 3.60 | 69.00
Any feedback will be appreciated.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p454914...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-04 13:41 Harald Fuchs <hari.fuchs@gmail.com>
parent: gmb <gmbouwer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Harald Fuchs @ 2011-07-04 13:41 UTC (permalink / raw)
To: pgsql-sql
In article <1309762075448-4549140.post@n5.nabble.com>,
gmb <gmbouwer@gmail.com> writes:
> Hi I have a stored function returning a record consisting of two field,
> and receiving as input a single identifier: CREATE FUNCTION calcvalues(IN
> itemid VACHAR, OUT calcval1 NUMERIC, OUT calcval2 NUMERIC) RETURNS
> record... Now, I want to use this function in a query as follows: SELECT
> itemid, calcvalues(itemid) FROM itemlist; which returns: itemid |
> calcvalues --------+------------- 4 | (0.67,10.00) 5 | (1.55,45.00) 6 |
> (3.60,69.00) Now, how should I go about to give the following output:
> itemid | calcval1 | calcval2 --------+----------+---------- 4 | 0.67 |
> 10.00 5 | 1.55 | 45.00 6 | 3.60 | 69.00 Any feedback will be appreciated.
SELECT itemid, (calcvalues(itemid)).* FROM itemlist
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-04 13:59 gmb <gmbouwer@gmail.com>
parent: Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: gmb @ 2011-07-04 13:59 UTC (permalink / raw)
To: pgsql-sql
Harald Fuchs-10 wrote:
> In article <1309762075448-4549140.post@n5.nabble.com>,gmb
> <gmbouwer@gmail.com> writes:
> SELECT itemid, (calcvalues(itemid)).* FROM itemlist
>
Thanks for the feedback, Harald.
How about specifying different aliases to the resulting values?
This will be handy when I use the same function multiple times in the same
query.
(the function will take another input parameters used in the calculations)
E.g.:
SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid,
'2011-06-07')).* FROM itemlist;
itemid | calcval1 | calcval2 | calcval1 | calcval2
--------+----------+----------+----------+----------
4 | 0.67 | 10.00 | 0.64 | 65.23
5 | 1.55 | 45.00 | 1.23 | 23.25
6 | 3.60 | 69.00 | 2.98 | 62.66
How will I manage unique column names for this output?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p455009...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-04 18:31 lists-pgsql@useunix.net
parent: gmb <gmbouwer@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: lists-pgsql@useunix.net @ 2011-07-04 18:31 UTC (permalink / raw)
To: pgsql-sql
Sorry, I don't have a useful answer but I have a similar question.
Along these same lines how does one access the discreet x,y components
of type 'point'?
On Mon, Jul 04, 2011 at 06:59:49AM -0700, gmb wrote:
>
> Harald Fuchs-10 wrote:
> > In article <1309762075448-4549140.post@n5.nabble.com>,gmb
> > <gmbouwer@gmail.com> writes:
> > SELECT itemid, (calcvalues(itemid)).* FROM itemlist
> >
>
> Thanks for the feedback, Harald.
>
> How about specifying different aliases to the resulting values?
> This will be handy when I use the same function multiple times in the same
> query.
> (the function will take another input parameters used in the calculations)
>
> E.g.:
> SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid,
> '2011-06-07')).* FROM itemlist;
>
> itemid | calcval1 | calcval2 | calcval1 | calcval2
> --------+----------+----------+----------+----------
> 4 | 0.67 | 10.00 | 0.64 | 65.23
> 5 | 1.55 | 45.00 | 1.23 | 23.25
> 6 | 3.60 | 69.00 | 2.98 | 62.66
> How will I manage unique column names for this output?
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p455009...
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> 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] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-04 19:28 lists-pgsql@useunix.net
parent: lists-pgsql@useunix.net
0 siblings, 0 replies; 8+ messages in thread
From: lists-pgsql@useunix.net @ 2011-07-04 19:28 UTC (permalink / raw)
To: pgsql-sql
Never mind, turns out you can do it with the array subscript operator. I
stumbled on to this by chance. I don't know if this is in the
documentation somewhere and I perhaps missed it?
On Mon, Jul 04, 2011 at 02:31:52PM -0400, lists-pgsql@useunix.net wrote:
> Sorry, I don't have a useful answer but I have a similar question.
>
> Along these same lines how does one access the discreet x,y components
> of type 'point'?
>
>
> On Mon, Jul 04, 2011 at 06:59:49AM -0700, gmb wrote:
> >
> > Harald Fuchs-10 wrote:
> > > In article <1309762075448-4549140.post@n5.nabble.com>,gmb
> > > <gmbouwer@gmail.com> writes:
> > > SELECT itemid, (calcvalues(itemid)).* FROM itemlist
> > >
> >
> > Thanks for the feedback, Harald.
> >
> > How about specifying different aliases to the resulting values?
> > This will be handy when I use the same function multiple times in the same
> > query.
> > (the function will take another input parameters used in the calculations)
> >
> > E.g.:
> > SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid,
> > '2011-06-07')).* FROM itemlist;
> >
> > itemid | calcval1 | calcval2 | calcval1 | calcval2
> > --------+----------+----------+----------+----------
> > 4 | 0.67 | 10.00 | 0.64 | 65.23
> > 5 | 1.55 | 45.00 | 1.23 | 23.25
> > 6 | 3.60 | 69.00 | 2.98 | 62.66
> > How will I manage unique column names for this output?
> >
> > --
> > View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p455009...
> > Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> 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] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-05 08:21 gmb <gmbouwer@gmail.com>
parent: gmb <gmbouwer@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: gmb @ 2011-07-05 08:21 UTC (permalink / raw)
To: pgsql-sql
gmb wrote:
>
>
> Thanks for the feedback, Harald.
>
> How about specifying different aliases to the resulting values?
> This will be handy when I use the same function multiple times in the same
> query.
> (the function will take another input parameters used in the calculations)
>
> E.g.:
> SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid,
> '2011-06-07')).* FROM itemlist;
>
> itemid | calcval1 | calcval2 | calcval1 | calcval2
> --------+----------+----------+----------+----------
> 4 | 0.67 | 10.00 | 0.64 | 65.23
> 5 | 1.55 | 45.00 | 1.23 | 23.25
> 6 | 3.60 | 69.00 | 2.98 | 62.66
> How will I manage unique column names for this output?
>
Hmm.. no takers? I guess not possible then?
Thanks anyway
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p455251...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-05 09:00 Pavel Stehule <pavel.stehule@gmail.com>
parent: gmb <gmbouwer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-07-05 09:00 UTC (permalink / raw)
To: gmb <gmbouwer@gmail.com>; +Cc: pgsql-sql
2011/7/5 gmb <gmbouwer@gmail.com>:
>
> gmb wrote:
>>
>>
>> Thanks for the feedback, Harald.
>>
>> How about specifying different aliases to the resulting values?
>> This will be handy when I use the same function multiple times in the same
>> query.
>> (the function will take another input parameters used in the calculations)
>>
>> E.g.:
>> SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid,
>> '2011-06-07')).* FROM itemlist;
>>
>> itemid | calcval1 | calcval2 | calcval1 | calcval2
>> --------+----------+----------+----------+----------
>> 4 | 0.67 | 10.00 | 0.64 | 65.23
>> 5 | 1.55 | 45.00 | 1.23 | 23.25
>> 6 | 3.60 | 69.00 | 2.98 | 62.66
>> How will I manage unique column names for this output?
>>
> Hmm.. no takers? I guess not possible then?
> Thanks anyway
>
hello
try to wrap your query to subselect,
npcps_201=# select 1,2,2,3;
?column? │ ?column? │ ?column? │ ?column?
──────────┼──────────┼──────────┼──────────
1 │ 2 │ 2 │ 3
(1 row)
Time: 0.171 ms
npcps_201=# select * from (select 1,2,2,3) x (a,b,c,d);
a │ b │ c │ d
───┼───┼───┼───
1 │ 2 │ 2 │ 3
(1 row)
Time: 0.202 ms
Regards
Pavel Stehule
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p455251...
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> 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] 8+ messages in thread
* Re: Usage of function retruning record in query
@ 2011-07-05 09:07 gmb <gmbouwer@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: gmb @ 2011-07-05 09:07 UTC (permalink / raw)
To: pgsql-sql
Pavel Stehule wrote:
>
>
> npcps_201=# select * from (select 1,2,2,3) x (a,b,c,d);
>
>
That is a neat trick - one I didn't know of.
Thanks Pavel
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p455261...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-07-05 09:07 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-04 06:47 Usage of function retruning record in query gmb <gmbouwer@gmail.com>
2011-07-04 13:41 ` Harald Fuchs <hari.fuchs@gmail.com>
2011-07-04 13:59 ` gmb <gmbouwer@gmail.com>
2011-07-04 18:31 ` lists-pgsql@useunix.net
2011-07-04 19:28 ` lists-pgsql@useunix.net
2011-07-05 08:21 ` gmb <gmbouwer@gmail.com>
2011-07-05 09:00 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-07-05 09:07 ` gmb <gmbouwer@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox