pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgsql: function passing argument to IN operator
2+ messages / 2 participants
[nested] [flat]
* PL/pgsql: function passing argument to IN operator
@ 2007-12-29 06:40 Daniel Myers <danielsmyers@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Daniel Myers @ 2007-12-29 06:40 UTC (permalink / raw)
To: 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...
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PL/pgsql: function passing argument to IN operator
@ 2007-12-29 07:29 Pavel Stehule <pavel.stehule@gmail.com>
parent: Daniel Myers <danielsmyers@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2007-12-29 07:29 UTC (permalink / raw)
To: Daniel Myers <danielsmyers@gmail.com>; +Cc: 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@gmail.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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-12-29 07:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-29 06:40 PL/pgsql: function passing argument to IN operator Daniel Myers <danielsmyers@gmail.com>
2007-12-29 07:29 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox