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

From: Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: is there a way to deliver an array over column from a query window?
Date: 2013-03-24 10:46:11
Message-ID: 514ED973.2070006@zorro.isa-geek.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Misa Simic 2013-03-24 11:06:42 Re: is there a way to deliver an array over column from a query window?
Previous Message Misa Simic 2013-03-24 10:26:37 Re: PostgreSQL and VIEWS