Re: Is This A Set Based Solution?

On Thu, 15 Mar 2007 09:47:27 -0500, gweaver(at)shaw(dot)ca (George Weaver)
wrote:
in <008001c76710$da487db0$6400a8c0(at)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