Tuesday 5 October 2010

How to aggregate arrays into an array in PostgreSQL

I had to aggregate a bunch of text into a nested array using PostgreSQL 8.3.x but I found no existing solution. Creating this aggregate function worked for me.

First, create function for concatenating two arrays:


CREATE OR REPLACE FUNCTION array_to_array_cat(anyarray, anyarray)
RETURNS anyarray AS
$BODY$
select $1 || $2;
$BODY$
LANGUAGE sql IMMUTABLE



Then create aggregate function that is based on array concatenation function:


CREATE AGGREGATE array_to_array_agg(anyarray) (
SFUNC = array_to_array_cat,
STYPE = anyarray
);



Now, let's test it.
Array concatenation function:


SELECT array_to_array_cat(ARRAY[[1,2,3]], ARRAY[[7,8,9]]); --"{{1,2,3},{7,8,9}}"

SELECT array_to_array_cat(ARRAY[['foo','bar']], ARRAY[['ice','cream']]); --"{{foo,bar},{ice,cream}}"


Aggregate arrays from a column in a nested array from 'mytable':


SELECT array_to_array_agg(ARRAY[[col_a::varchar, col_b::varchar, col_c::varchar, col_d::varchar]])
FROM mytable;



Might return something like:
"{{c.1270,1270,SAC,"Description of SAC"},{1672,1672,HTR,"Description of HTR"}}"

N.B. This will work only if nested arrays have equal number of elements!

Tuesday 10 August 2010

How to convert an array to a table?

This little function can explode array item into a table item

CREATE OR REPLACE FUNCTION explode_array(in_array anyarray)
RETURNS SETOF anyelement AS
$BODY$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$BODY$
LANGUAGE 'sql' IMMUTABLE
COST 100
ROWS 1000;


Example use:

=> select explode_array(array[1,2,3]);
explode_array
---------------
1
2
3
(3 rows)

ALTER TABLE ADD COLUMN IF NOT EXISTS OR ADD COLUMN WITH incremental suffix IF EXISTS

A generic way to add a new column in PostgreSQL 8.3 from plpgsql by incrementing column suffix in a case when column already exists.

DECLARE
query TEXT :='';
suffix INTEGER :=0;
recs INTEGER :=0;
BEGIN

LOOP
suffix = suffix + 1;
EXECUTE 'SELECT count(*) FROM information_schema.columns
WHERE table_schema||$$.$$||table_name = $$'||mytablename||'$$
AND column_name = $$mycolumn'||suffix||'$$' INTO recs;
RAISE INFO 'recs %!', recs;
IF recs = 0 THEN
EXECUTE 'ALTER TABLE '||tablename||' ADD COLUMN class'||suffix||' INTEGER';
RAISE INFO 'Column mycolumn% was added!', suffix;
EXIT;
END IF;
END LOOP;