Re: Passing a list of values to a function

[Please don't use a blank subject. I've added a subject to this
message.]

On Mon, Jan 09, 2006 at 03:26:45PM -0800, Matthew Peter wrote:
> How come when I pass in a list in it doesn't use it as a list of
> integers? Do I need to somehow make $1 be interpreted as a list of
> ints? Rather than just passing a text value that contains the list?
>
> CREATE TABLE my_tbl (u_id int);
>
> INSERT INTO my_tbl (u_id) values (1);
> INSERT INTO my_tbl (u_id) values (2);
> INSERT INTO my_tbl (u_id) values (3);
>
> CREATE OR REPLACE FUNCTION getlist(text) RETURNS SETOF my_tbl as $$
> SELECT * FROM my_tbl
> WHERE u_id IN (0, $1);
>
> $$ LANGUAGE SQL;
>
> SELECT * from getlist('1,2,3');
> (0 rows)

You've passed a text argument so you're getting a query plan like
this:

test=> EXPLAIN SELECT * FROM my_tbl WHERE u_id IN (0, '1,2,3'::text);
QUERY PLAN
----------------------------------------------------------
Seq Scan on my_tbl (cost=0.00..1.05 rows=2 width=4)
Filter: ((u_id = 0) OR ((u_id)::text = '1,2,3'::text))
(2 rows)

You could write the function to take an array of integers instead
of a text string:

CREATE OR REPLACE FUNCTION getlist(integer[]) RETURNS SETOF my_tbl as $$
SELECT * FROM my_tbl
WHERE u_id = 0 OR u_id = ANY($1)
$$ LANGUAGE SQL STABLE STRICT;

SELECT * FROM getlist('{1,2,3}');

Another way would be to build a query string in a PL/pgSQL function
and use EXECUTE, but beware of embedding function arguments in query
strings without quoting.

--
Michael Fuhr