maybe, SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by invoice_nr) from invoices;
2013/3/24 Rafał Pietrak <ra...@zorro.isa-geek.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
