Wednesday, July 25th, 2007...9:27 am

Postgres Array Joins

Jump to Comments

Every once in a while you'll come across the need to create a temp table to perform a complicated select query. Not being a big fan of creating these temp tables, I came up with a better solution. Leveraging Postgresql's array structure, I wrote a function that converts a varchar[] to a record set.

CREATE OR REPLACE FUNCTION array_to_rows( character varying[])
RETURNS SETOF character varying AS
$BODY$
DECLARE
in_array alias FOR $1;

out_varchar varchar;
BEGIN
FOR i IN 1..array_upper(in_array,1) loop
RETURN next in_array[i];
end loop;

RETURN;
END
$BODY$
LANGUAGE 'plpgsql' STABLE;

This allows me to pass in an unknown amount of data and perform a LEFT OUTER JOIN on it without losing my orignal data.

SELECT

a.name, b.name, b.other_info

FROM

(SELECT array_to_rows AS name FROM array_to_rows(ARRAY['JOE', 'SCOTT', 'BOB']) a

LEFT OUTER JOIN names b ON a.name ilike '%' || b.name || '%'

Hope you find this useful!

1 Comment

Filed under postgres

1 Comment

Leave a Reply