How to cast a result set into an array?

arrays, plpgsql, postgresql, postgresql-8.4, sql

Solution

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.

Problem

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?

Original source