Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux