Is This A Set Based Solution?

[Stefan Berglund <stefan_berglund@xxxxxxx>]

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