Search Postgresql Archives

Is This A Set Based Solution?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux