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

maybe,

SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
invoice_nr) from invoices;

2013/3/24 Rafał Pietrak <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)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>