Re: Is This A Set Based Solution?

[Stefan Berglund <sorry.no.koolaid@xxxxxx>]

On Thu, 15 Mar 2007 09:47:27 -0500, gweaver@xxxxxxx (George Weaver)
wrote:
 in <008001c76710$da487db0$6400a8c0@Dell4500> 

>
>Stefan Berglund wrote:
>
>> foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
>> alternatively pass the string of IDs ('53016,27,292,512') to a table
>> returning function which TABLE is then JOINed with the table I wish to
>
>The user selections will be in some sort of list.  Could you not use WHERE 
>ID IN (the list)?

Coming from SQL Server where that is not allowed, it didn't occur to me
that PostgreSQL would allow a substitutable parameter in the IN clause. 
However, it seems that it can't be done in this fashion without using
dynamic SQL unless I'm missing something.

I tried this:

create or replace function foo(plist TEXT)
  RETURNS SETOF Show_Entries as $$

  SELECT *
  FROM Show_Entries
  WHERE Show_ID = 1250 AND Show_Number IN ($1);

$$ LANGUAGE sql;

When I use select * from foo('101,110,115,120'); I get no results.  When
I use select * from foo(101,110,115,120); I get the correct results.

At any rate, I'm happy with what I've come up with and so far
performance is excellent:

CREATE TABLE test_table (
  id int 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 foo (
  pList TEXT) RETURNS SETOF INTEGER AS $foo$

DECLARE
  v_arr text[];

BEGIN
  v_arr := string_to_array($1, ',');
  FOR i IN array_lower(v_arr, 1)..array_upper(v_arr, 1) LOOP
    RETURN NEXT v_arr[i]::int;
  END LOOP;
RETURN;
END;
$foo$ LANGUAGE plpgsql;

SELECT *
FROM
  foo('5,1,3') SL INNER JOIN
  test_table T ON SL=T.ID;

SELECT * FROM foo('52001,17,22,42,47') ORDER BY foo;

---
Stefan Berglund