How to cast a result set into an array?

Question

I'm using PostgreSQL 8.4 and writing a function as follows. I came across an issue, how to cast a result set into an array. I mean, imagine I have a query returning only one column of type integer, like

SELECT amount from daily_profit;

I've been trying to write something like the following:

CREATE OR REPLACE FUNCTION fill_daily_profit() RETURNS void AS $$
DECLARE
    arr integer[] := cast ((SELECT amount from partner.daily_profit) as integer[]);     
    -- doesn't work, the following error was produced:
    -- cannot cast type integer to integer[]
BEGIN
END $$
LANGUAGE plpgsql;

Any ideas?

Answer 1

I suggest a simpler and faster ARRAY constructor for this:

CREATE OR REPLACE FUNCTION fill_daily_profit()
 RETURNS void AS
$func$
DECLARE
   arr integer[] := ARRAY (SELECT amount FROM partner_daily_profit);
BEGIN
   ...
END
$func$  LANGUAGE plpgsql;

Add an ORDER BY clause to the SELECT if you want elements in a particular order.

However, There is often a set-based solution around the corner that voids the need for such an array a priori.

Answer 2

You need to aggregate the values into an array:

CREATE OR REPLACE FUNCTION fill_daily_profit() RETURNS void AS $$
DECLARE
    arr integer[];     
BEGIN
  select array_agg(amount)
     into arr
  from partner_daily_profit;
END $$
LANGUAGE plpgsql;