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