Re: is there a way to deliver an array over column from a query window?

W dniu 03/24/2013 12:06 PM, Misa Simic pisze:
> maybe,
>
> SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
> invoice_nr) from invoices;

RIGHT. Thenx. (and the first thing I did, I've read the doc on
array_agg().... what stress makes from people :(

thenx again,

-R

>
>
> 2013/3/24 Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com
> <mailto:rafal(at)zorro(dot)isa-geek(dot)com>>
>
> Hi,
>
> I really don't know how to ask for what I'm looking for; but I
> think, may be, calling it an ARRAY yielding aggregate function for
> use within a query WINDOW would do?
>
> I'm looking for something like:
>
> SELECT DISTINCT issuer,amount, array(REFERENCE) over (partition by
> invoice_nr) from invoices;
>
> where the invoices table contain the usual invoice data, like:
> - amount
> - time of issue
> - the issuer ID
> - and the above REFERENCE, is the reference to a bank transfer
> record in another table.
>
> The thing is, that I have multiple funds transfer records
> referring to a single invoice; and (I currently think, that :) I
> need to fetch them all in a single column - so preferrably within
> an ARRAY of references (or just transfer IDs) to table containing
> funds transfer records. In other words, I wouldn't like to
> collapse the query at the application level ... but "if everything
> else fails", that is the last resort I keep in mind.
>
> The reason I'm not taking the last resort now, is that I also have
> multiple invoices to a single funds transfer record. And
> ultimately I'm hoping to cook a query, that would
> window-and-balance the two tables (invoices, and funds transfer) -
> so that most of the job is done by SQL/funciton/view, not by the
> external application.
>
> I've looked up postgres documentation, looking for a sort of
> "aggregate" function, that would not compute anything, but just
> build an ARRAY from all its input, but couldn't locate any.
>
> Is there a way to achieve this in postgresql?
>
> -R
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>