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@horseshowtime.com
> 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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
