[SQL] 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: 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.
Re: [SQL] Usage of function retruning record in query
Harald Fuchs-10 wrote: > In article <[email protected]>,gmb > <[email protected]> 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
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. -- 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
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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] UNNEST result order vs Array data
Hi all I just want to confirm something regarding UNNEST function used with arrays. I cannot see that it is specifically mentioned in the documentation , but maybe because it is obvious. Is the order of the result guaranteed to be the order of the array I.e. is it possible that: SELECT UNNEST( ARRAY[1,2,3] ) will sometimes return: unnest 2 1 3 (3 rows) instead of: unnest 1 2 3 (3 rows) Help will be appreciated -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087.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] UNNEST result order vs Array data
Hi Vik Thanks for the reply. Can you please give me an example of how the order is specified? I want the result of the UNNEST to be in the order of the array field E.g. SELECT UNNEST ( ARRAY[ 'abc' , 'ggh' , '12aa' , '444f' ] ); Should always return: unnest abc ggh 12aa 444f How should the ORDER BY be implemented in the syntax? Regards -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760092.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] UNNEST result order vs Array data
>> An array is ordered. If you turn it into a relation then it isn't >> ordered any more until you apply ORDER BY. >> >> An unnest() will almost certainly scan the array in-order, but once you >> embed that in a large query the ordering is no longer guaranteed. Thanks, I assumed as much. Appreciate your help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760106.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] UNNEST result order vs Array data
>> The best, which you won't >> like, is to wait for 9.4 where unnest() will most likely have a WITH >> ORDINALITY option and you can sort on that. The fact that this type of thing is on the 9.4 roadmap indicates (to me, in any case) that there are problems with the UNNEST functionality in the current version (I'm running 9.2). Thanks Vik, I'll take a look at the implementation you suggested. -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760107.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] Help needed with Window function
David Johnston wrote > Basic idea: use ORDER BY in the window to auto-define a range-preceding > frame. Create an array of all dates (tags in the example) that match with > positive amounts. Negative amounts get their matching tag added to the > array as NULL. The provided function looks into the generated array and > returns the last (closest to the current row in the frame) non-null > date/tag in the array which ends up being the date/tag matching the last > positive amount in the frame. > > David J. Hi David, Thanks for your reply. This is an approach I also considered, but hoped for a solution without the expense (albeit small) of having to create a function. Just wanted to confirm that I'm not missing a simpler solution (my knowledge in terms of window functions is rather limited). Until something better comes along, I'll implement the solution as suggested here. Regards GMB -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-needed-with-Window-function-tp5773160p5773196.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
