Re: Is This A Set Based Solution?

Stefan Berglund <sorry.no.koolaid@for.me> writes:
> 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.

Just for the record, the reason that didn't work is that Postgres saw it
as a comparison to a single scalar IN-list item.  What you had was
effectively

   WHERE Show_ID = 1250 AND Show_Number::text IN ('101,110,115,120');

which of course will fail to find any rows.

In recent releases (8.2 for sure, don't remember if 8.1 can do this
efficiently) you could instead do

create or replace function foo(plist int[])
  RETURNS SETOF Show_Entries as $$

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

$$ LANGUAGE sql;

select * from foo(array[101,110,115,120]);

            regards, tom lane