On Thu, 15 Mar 2007 09:47:27 -0500, gweaver@shaw.ca (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
