Re: Is This A Set Based Solution?

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