Lists: | pgsql-sql |
---|
From: | "Daniel Myers" <danielsmyers(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | PL/pgsql: function passing argument to IN operator |
Date: | 2007-12-29 06:40:03 |
Message-ID: | 455d64770712282240h3c93a2f0y9e357d1d23882b10@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Hello list,
I'm trying to write a function that uses one of its arguments as
an input to an IN operator in a WHERE clause. I.e., something like:
-- ids are actually integers
CREATE FUNCTION foo(ids SOMETYPE) RETURNS [whatever] AS $$
BEGIN
SELECT id, [other stuff] FROM table WHERE [other conditions] AND
id IN (ids);
RETURN;
END
$$ plpgsql
I had wanted to avoid using the EXECUTE statement and dynamically
creating the query string, because 1) I'd rather not recompile the
query each time and 2) I'm doing a SELECT INTO, which apparently
doesn't work with EXECUTE.
Can anyone help me out?
Thanks,
Daniel
P.S.: Sorry if capitalizing SQL keywords is bad list etiquette...
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Daniel Myers" <danielsmyers(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: PL/pgsql: function passing argument to IN operator |
Date: | 2007-12-29 07:29:22 |
Message-ID: | 162867790712282329x2ff4d99cyd8057ec6aca76d70@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Hello
it has sense only for array type
so you can try:
create replace function foo(anyarray)
returns bool as $$
begin
return (select 1 = any($1));
end ; $$ language plpgsql;
postgres=# select foo(array[1,2,3]);
foo
-----
t
(1 row)
postgres=# select foo(array[2,3]);
foo
-----
f
(1 row)
Regards
Pavel Stehule
On 29/12/2007, Daniel Myers <danielsmyers(at)gmail(dot)com> wrote:
> Hello list,
> I'm trying to write a function that uses one of its arguments as
> an input to an IN operator in a WHERE clause. I.e., something like:
>
> -- ids are actually integers
> CREATE FUNCTION foo(ids SOMETYPE) RETURNS [whatever] AS $$
> BEGIN
> SELECT id, [other stuff] FROM table WHERE [other conditions] AND
> id IN (ids);
> RETURN;
> END
> $$ plpgsql
>
> I had wanted to avoid using the EXECUTE statement and dynamically
> creating the query string, because 1) I'd rather not recompile the
> query each time and 2) I'm doing a SELECT INTO, which apparently
> doesn't work with EXECUTE.
>
> Can anyone help me out?
>
> Thanks,
> Daniel
>
> P.S.: Sorry if capitalizing SQL keywords is bad list etiquette...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>