I don't know if you could change your schema. but I'd consider your
problem as a overlapping arrays task and use contrib/intarray for that.
Oleg
On Fri, 9 Mar 2007, Stefan Berglund wrote:
Hi-
Below is a small test case that illustrates what I'm attempting which is
to provide a comma separated list of numbers to a procedure which
subsequently uses this list in a join with another table.
My questions are is this a set based solution and is this the best
approach in terms of using the data types and methods afforded by
PostgreSQL? I'm mostly inquiring about the double FOR loop which just
doesn't feel right to me and I'd also like to feel that I'm generally on
the right track before converting the other 400 procedures from SQL
Server 2000 to PostgreSQL.
CREATE TYPE fn_return_int4 AS (N int);
CREATE TABLE test_table (
id SMALLINT not null,
tname varchar(50) not null);
INSERT INTO test_table
SELECT 1, 'Adams'
UNION SELECT 2, 'Baker'
UNION SELECT 3, 'Chrysler'
UNION SELECT 4, 'Douglas'
UNION SELECT 5, 'Everyman';
CREATE OR REPLACE FUNCTION fn_Split_List (
pList TEXT) RETURNS SETOF fn_return_int4 AS $fn_Split_List$
DECLARE
v_row fn_return_int4%rowtype;
v_list alias for $1;
v_delim text := ',';
v_arr text[];
BEGIN
v_arr := string_to_array(v_list, v_delim);
FOR i IN array_lower(v_arr, 1)..array_upper(v_arr, 1) LOOP
FOR v_row IN SELECT v_arr[i] LOOP
RETURN NEXT v_row;
END LOOP;
END LOOP;
RETURN;
END;
$fn_Split_List$ LANGUAGE plpgsql;
SELECT *
FROM
fn_Split_List('5,1,3') SL INNER JOIN
test_table T ON SL.N=T.ID;
I did discover that I was able to define the function with a native type
but then the usage looked a little odd:
SELECT *
FROM
fn_Split_List('5,1,3') SL INNER JOIN
test_table T ON SL=T.ID;
Stefan Berglund
www.horseshowtime.com
Online Show Entry - Instant Internet Horse Show Schedules and Results
stefan@xxxxxxxxxxxxxxxxx
tel 714.968.9112 fax 714.968.5940
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
