From gmbouwer@gmail.com Mon Jul 4 06:48:02 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 92AA3B5D91F for ; Mon, 4 Jul 2011 03:48:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 97598-07 for ; Mon, 4 Jul 2011 06:47:56 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from sam.nabble.com (sam.nabble.com [216.139.236.26]) by mail.postgresql.org (Postfix) with ESMTP id 0FAD8B5D80A for ; Mon, 4 Jul 2011 03:47:55 -0300 (ADT) Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1Qdcwl-0002mT-Eh for pgsql-sql@postgresql.org; Sun, 03 Jul 2011 23:47:55 -0700 Date: Sun, 3 Jul 2011 23:47:55 -0700 (PDT) From: gmb To: pgsql-sql@postgresql.org Message-ID: <1309762075448-4549140.post@n5.nabble.com> Subject: Usage of function retruning record in query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_601_9792886.1309762075450" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.766 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/1 X-Sequence-Number: 35571 ------=_Part_601_9792886.1309762075450 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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-tp4549140p4549140.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ------=_Part_601_9792886.1309762075450 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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: Usage of function retruning record in query
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
------=_Part_601_9792886.1309762075450-- From hari.fuchs@gmail.com Mon Jul 4 13:41:32 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E11B2B5DBCA for ; Mon, 4 Jul 2011 10:41:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 59537-01 for ; Mon, 4 Jul 2011 13:41:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id 66698B5D8C5 for ; Mon, 4 Jul 2011 10:41:26 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1QdjOv-0007iv-E1 for pgsql-sql@postgresql.org; Mon, 04 Jul 2011 15:41:25 +0200 Received: from 212.126.222.122 ([212.126.222.122]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 04 Jul 2011 15:41:25 +0200 Received: from hari.fuchs by 212.126.222.122 with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 04 Jul 2011 15:41:25 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: Harald Fuchs Subject: Re: Usage of function retruning record in query Date: Mon, 04 Jul 2011 15:41:12 +0200 Lines: 14 Message-ID: <868vseduyv.fsf@mgm.protecting.net> References: <1309762075448-4549140.post@n5.nabble.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: 212.126.222.122 X-Archive: encrypt User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux) Cancel-Lock: sha1:ChtAin1TJml7Pb9HvPuscj5y1nc= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.735 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_NUMERIC_HELO=1.164, T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201107/2 X-Sequence-Number: 35572 In article <1309762075448-4549140.post@n5.nabble.com>, gmb 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 From gmbouwer@gmail.com Mon Jul 4 13:59:56 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5E0F9B5DBCA for ; Mon, 4 Jul 2011 10:59:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 22865-07 for ; Mon, 4 Jul 2011 13:59:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from sam.nabble.com (sam.nabble.com [216.139.236.26]) by mail.postgresql.org (Postfix) with ESMTP id 1D0E1B5D8C5 for ; Mon, 4 Jul 2011 10:59:50 -0300 (ADT) Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1Qdjgj-00027d-5W for pgsql-sql@postgresql.org; Mon, 04 Jul 2011 06:59:49 -0700 Date: Mon, 4 Jul 2011 06:59:49 -0700 (PDT) From: gmb To: pgsql-sql@postgresql.org Message-ID: <1309787989162-4550092.post@n5.nabble.com> In-Reply-To: <868vseduyv.fsf@mgm.protecting.net> References: <1309762075448-4549140.post@n5.nabble.com> <868vseduyv.fsf@mgm.protecting.net> Subject: Re: Usage of function retruning record in query MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.767 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/3 X-Sequence-Number: 35573 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. From lists-pgsql@useunix.net Mon Jul 4 18:32:15 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 1DA17B5DBDC for ; Mon, 4 Jul 2011 15:32:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 91364-09 for ; Mon, 4 Jul 2011 18:32:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from eastrmfepo101.cox.net (eastrmfepo101.cox.net [68.230.241.213]) by mail.postgresql.org (Postfix) with ESMTP id 44DC8B5DBD6 for ; Mon, 4 Jul 2011 15:32:06 -0300 (ADT) Received: from eastrmimpo01.cox.net ([68.1.16.119]) by eastrmfepo101.cox.net (InterMail vM.8.01.04.00 201-2260-137-20101110) with ESMTP id <20110704183157.XNS3772.eastrmfepo101.cox.net@eastrmimpo01.cox.net> for ; Mon, 4 Jul 2011 14:31:57 -0400 Received: from slacker.ja10629.home ([68.106.109.147]) by eastrmimpo01.cox.net with bizsmtp id 3uXt1h0073Ar1Vg02uXtMu; Mon, 04 Jul 2011 14:31:57 -0400 X-CT-Score: 0.00 X-CT-RefID: str=0001.0A020205.4E12071D.001B,ss=1,re=0.000,fgs=0 X-CT-Spam: 0 X-Authority-Analysis: v=1.1 cv=+zmVqi6HbhBxOY6lzrsfnB2wShF2MeCUcHVIQWL6sOU= c=1 sm=1 a=z1TLwsU0kBEA:10 a=bzbpU1KvNssA:10 a=PjkiJtDTOQ4A:10 a=ZcFhQy0-F_sA:10 a=kj9zAlcOel0A:10 a=sXZhTwfwSKhiTs9RzZXpNQ==:17 a=9I5xiGouAAAA:8 a=pGLkceISAAAA:8 a=epTmVMiNAAAA:8 a=L9jOsvaEBPzVcULA6VwA:9 a=ibNz_0jkkAnWDP5MFPgA:7 a=CjuIK1q_8ugA:10 a=Du3yRyqOjqsA:10 a=2fPOlPt4dusA:10 a=MSl-tDqOz04A:10 a=YObXiYvaVLwA:10 a=sXZhTwfwSKhiTs9RzZXpNQ==:117 X-CM-Score: 0.00 Authentication-Results: cox.net; none Received: from wcuddy by slacker.ja10629.home with local (Exim 4.72) (envelope-from ) id 1Qdnw1-0005MS-03 for pgsql-sql@postgresql.org; Mon, 04 Jul 2011 14:31:53 -0400 Date: Mon, 4 Jul 2011 14:31:52 -0400 From: lists-pgsql@useunix.net To: pgsql-sql@postgresql.org Subject: Re: Usage of function retruning record in query Message-ID: <20110704183152.GJ13621@slacker.ja10629.home> References: <1309762075448-4549140.post@n5.nabble.com> <868vseduyv.fsf@mgm.protecting.net> <1309787989162-4550092.post@n5.nabble.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1309787989162-4550092.post@n5.nabble.com> User-Agent: Mutt/1.4.2.3i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.778 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/4 X-Sequence-Number: 35574 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 From lists-pgsql@useunix.net Mon Jul 4 19:28:12 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 11C22B5DC04 for ; Mon, 4 Jul 2011 16:28:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 36887-04 for ; Mon, 4 Jul 2011 19:28:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from eastrmfepo202.cox.net (eastrmfepo202.cox.net [68.230.241.217]) by mail.postgresql.org (Postfix) with ESMTP id 6104AB5DBFD for ; Mon, 4 Jul 2011 16:28:05 -0300 (ADT) Received: from eastrmimpo02.cox.net ([68.1.16.120]) by eastrmfepo202.cox.net (InterMail vM.8.01.04.00 201-2260-137-20101110) with ESMTP id <20110704192804.UJUM32466.eastrmfepo202.cox.net@eastrmimpo02.cox.net> for ; Mon, 4 Jul 2011 15:28:04 -0400 Received: from slacker.ja10629.home ([68.106.109.147]) by eastrmimpo02.cox.net with bizsmtp id 3vU11h00M3Ar1Vg02vU1GR; Mon, 04 Jul 2011 15:28:04 -0400 X-CT-Score: 0.00 X-CT-RefID: str=0001.0A020204.4E121444.008F,ss=1,re=0.000,fgs=0 X-CT-Spam: 0 X-Authority-Analysis: v=1.1 cv=5b/QVZoDi0k1AX8Vn5wfxQWDHHxdqMUGvpDELxVbSDQ= c=1 sm=1 a=z1TLwsU0kBEA:10 a=bzbpU1KvNssA:10 a=PjkiJtDTOQ4A:10 a=ZcFhQy0-F_sA:10 a=kj9zAlcOel0A:10 a=sXZhTwfwSKhiTs9RzZXpNQ==:17 a=P6M1L9rJAAAA:8 a=9I5xiGouAAAA:8 a=pGLkceISAAAA:8 a=epTmVMiNAAAA:8 a=GFtSlv9eCshLmAo-1hkA:9 a=--21CyPYynb7vLtJrA0A:7 a=CjuIK1q_8ugA:10 a=Du3yRyqOjqsA:10 a=2fPOlPt4dusA:10 a=V_iEmhlNuOsA:10 a=MSl-tDqOz04A:10 a=YObXiYvaVLwA:10 a=sXZhTwfwSKhiTs9RzZXpNQ==:117 X-CM-Score: 0.00 Authentication-Results: cox.net; none Received: from wcuddy by slacker.ja10629.home with local (Exim 4.72) (envelope-from ) id 1QdooL-0005SB-GK for pgsql-sql@postgresql.org; Mon, 04 Jul 2011 15:28:01 -0400 Date: Mon, 4 Jul 2011 15:28:01 -0400 From: lists-pgsql@useunix.net To: pgsql-sql@postgresql.org Subject: Re: Usage of function retruning record in query Message-ID: <20110704192801.GK13621@slacker.ja10629.home> References: <1309762075448-4549140.post@n5.nabble.com> <868vseduyv.fsf@mgm.protecting.net> <1309787989162-4550092.post@n5.nabble.com> <20110704183152.GJ13621@slacker.ja10629.home> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20110704183152.GJ13621@slacker.ja10629.home> User-Agent: Mutt/1.4.2.3i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.778 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/5 X-Sequence-Number: 35575 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 From gmbouwer@gmail.com Tue Jul 5 08:21:20 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id AD0FDB5DBE6 for ; Tue, 5 Jul 2011 05:21:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 40956-02 for ; Tue, 5 Jul 2011 08:21:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from sam.nabble.com (sam.nabble.com [216.139.236.26]) by mail.postgresql.org (Postfix) with ESMTP id 713B9B5DBCB for ; Tue, 5 Jul 2011 05:21:14 -0300 (ADT) Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1Qe0sb-00053E-P0 for pgsql-sql@postgresql.org; Tue, 05 Jul 2011 01:21:13 -0700 Date: Tue, 5 Jul 2011 01:21:13 -0700 (PDT) From: gmb To: pgsql-sql@postgresql.org Message-ID: <1309854073767-4552513.post@n5.nabble.com> In-Reply-To: <1309787989162-4550092.post@n5.nabble.com> References: <1309762075448-4549140.post@n5.nabble.com> <868vseduyv.fsf@mgm.protecting.net> <1309787989162-4550092.post@n5.nabble.com> Subject: Re: Usage of function retruning record in query MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.767 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/6 X-Sequence-Number: 35576 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. From pavel.stehule@gmail.com Tue Jul 5 09:01:54 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 13AC4B5DC17 for ; Tue, 5 Jul 2011 06:01:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 74478-01-4 for ; Tue, 5 Jul 2011 09:01:47 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 4E361B5DBE8 for ; Tue, 5 Jul 2011 06:01:38 -0300 (ADT) Received: by iwn6 with SMTP id 6so5027527iwn.19 for ; Tue, 05 Jul 2011 02:01:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; bh=mn/wtD5m553jk8R8dgU2c/tqdSdOsWOpM1mNFOgrTWc=; b=SlWH3QXJCNYNI6LF/rvv/GfDXtSiH1JUEzlkOMLNO0gCN6LQATh7VkThpSQBBDmh1c qymk/ioOSeuOysVqWXEz/sTLB2a5NYkXowkijJKYp+KrnNKUM6ZArf+xElK/PPLgpuQJ RtY46jH5gF98Hlo8Ie0eK5X3KToPIhw19Dw7o= Received: by 10.42.1.211 with SMTP id 19mr4877904ich.81.1309856498054; Tue, 05 Jul 2011 02:01:38 -0700 (PDT) MIME-Version: 1.0 Received: by 10.42.4.16 with HTTP; Tue, 5 Jul 2011 02:00:58 -0700 (PDT) In-Reply-To: <1309854073767-4552513.post@n5.nabble.com> References: <1309762075448-4549140.post@n5.nabble.com> <868vseduyv.fsf@mgm.protecting.net> <1309787989162-4550092.post@n5.nabble.com> <1309854073767-4552513.post@n5.nabble.com> From: Pavel Stehule Date: Tue, 5 Jul 2011 11:00:58 +0200 Message-ID: Subject: Re: Usage of function retruning record in query To: gmb Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.777 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/7 X-Sequence-Number: 35577 2011/7/5 gmb : > > 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 sa= me >> query. >> (the function will take another input parameters used in the calculation= s) >> >> E.g.: >> SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid, >> '2011-06-07')).* FROM itemlist; >> >> =C2=A0itemid | calcval1 | calcval2 | calcval1 | calcval2 >> --------+----------+----------+----------+---------- >> =C2=A0 =C2=A0 =C2=A0 4 | 0.67 =C2=A0 =C2=A0 | 10.00 =C2=A0 =C2=A0| 0.64 = =C2=A0 =C2=A0 | 65.23 >> =C2=A0 =C2=A0 =C2=A0 5 | 1.55 =C2=A0 =C2=A0 | 45.00 =C2=A0 =C2=A0| 1.23 = =C2=A0 =C2=A0 | 23.25 >> =C2=A0 =C2=A0 =C2=A0 6 | 3.60 =C2=A0 =C2=A0 | 69.00 =C2=A0 =C2=A0| 2.98 = =C2=A0 =C2=A0 | 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=3D# select 1,2,2,3; ?column? =E2=94=82 ?column? =E2=94=82 ?column? =E2=94=82 ?column? =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80 1 =E2=94=82 2 =E2=94=82 2 =E2=94=82 3 (1 row) Time: 0.171 ms npcps_201=3D# select * from (select 1,2,2,3) x (a,b,c,d); a =E2=94=82 b =E2=94=82 c =E2=94=82 d =E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2= =94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80 1 =E2=94=82 2 =E2=94=82 2 =E2=94=82 3 (1 row) Time: 0.202 ms Regards Pavel Stehule > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Usa= ge-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 > From gmbouwer@gmail.com Tue Jul 5 09:07:52 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 89A1EB5DBD0 for ; Tue, 5 Jul 2011 06:07:52 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 77561-02 for ; Tue, 5 Jul 2011 09:07:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from sam.nabble.com (sam.nabble.com [216.139.236.26]) by mail.postgresql.org (Postfix) with ESMTP id 4317AB5DBCA for ; Tue, 5 Jul 2011 06:07:46 -0300 (ADT) Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1Qe1bd-0000wW-Oh for pgsql-sql@postgresql.org; Tue, 05 Jul 2011 02:07:45 -0700 Date: Tue, 5 Jul 2011 02:07:45 -0700 (PDT) From: gmb To: pgsql-sql@postgresql.org Message-ID: <1309856865759-4552618.post@n5.nabble.com> In-Reply-To: References: <1309762075448-4549140.post@n5.nabble.com> <868vseduyv.fsf@mgm.protecting.net> <1309787989162-4550092.post@n5.nabble.com> <1309854073767-4552513.post@n5.nabble.com> Subject: Re: Usage of function retruning record in query MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.767 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201107/8 X-Sequence-Number: 35578 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.