Обсуждение: Usage of function retruning record in query
Hi I have a stored function returning a record consisting of two field, and receiving as input a single identifier: CREATEFUNCTION calcvalues(IN itemid VACHAR, OUT calcval1 NUMERIC, OUT calcval2 NUMERIC) RETURNS record... Now, I want touse 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 thefollowing output: itemid | calcval1 | calcval2 --------+----------+---------- 4 | 0.67 | 10.00 5 | 1.55 | 45.00 6 | 3.60| 69.00 Any feedback will be appreciated. <br /><hr align="left" width="300" /> View this message in context: <a href="http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p4549140.html">Usage offunction retruning record in query</a><br /> Sent from the <a href="http://postgresql.1045698.n5.nabble.com/PostgreSQL-sql-f2142323.html">PostgreSQL- sql mailing list archive</a> at Nabble.com.<br/>
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
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-tp4549140p4550092.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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-tp4549140p4550092.html > 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
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-tp4549140p4550092.html > > 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
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-tp4549140p4552513.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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-tp4549140p4552513.html > 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 >
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-tp4549140p4552618.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.