Re: [SQL] Usage of function retruning record in query

2011-07-04 Thread Harald Fuchs 
In article <[email protected]>,
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


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Usage of function retruning record in query

2011-07-04 Thread gmb 

Harald Fuchs-10 wrote:
> In article <[email protected]>,gmb
>  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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Usage of function retruning record in query

2011-07-04 Thread lists-pgsql 
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 <[email protected]>,gmb
> >  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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Usage of function retruning record in query

2011-07-04 Thread lists-pgsql 
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, [email protected] 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 <[email protected]>,gmb
> > >  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 ([email protected])
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> 
> -- 
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql