|
|
|
|
|
|
| Author |
Message |
Curtis Scheer
*nix forums beginner
Joined: 18 Jul 2006
Posts: 2
|
 Posted: Tue Jul 18, 2006 4:27 pm Post subject:
How to pass array of values to a pgplsql function
|
|
|
Does anyone have any examples of how I would make a stored procedure in
plpgsql that would allow for passing a list or arrays of values to be used
in an sql IN clause? Like so: select * from table where field1 in (values).
Is this possible?
Thanks,
Curtis
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
 |
Erik Jones
*nix forums beginner
Joined: 12 May 2006
Posts: 10
|
| Posted: Tue Jul 18, 2006 6:30 pm Post subject:
Re: How to pass array of values to a pgplsql function
|
|
|
Curtis Scheer wrote:
| Quote: | Does anyone have any examples of how I would make a stored procedure in
plpgsql that would allow for passing a list or arrays of values to be used
in an sql IN clause? Like so: select * from table where field1 in (values).
Is this possible?
Well, a good thing to note here is that there is a very distinct |
semantic difference between an array in postgres and what IN clauses
take as input: and array
is a data type whereas IN clauses take a parenthesized list of comma
separated values. So, if you pass an array into a function wherein
you then need
to use those values in an IN clause, you can build yourself an string of
the values in the array, comma separated of course.
e.g.
CREATE OR REPLACE FUNCTION somefunc(ids int[]) RETURNS boolean AS $sf$
DECLARE
in_values varchar;
good int;
BEGIN
FOR i IN array_upper(ids, 1) LOOP
in_values := in_values || ids[i] || ',';
END LOOP;
in_values := substring(in_values FROM 1 FOR
character_length(in_values) - 1); -- this will chop off the last comma
EXECUTE 'SELECT 1 FROM blah WHERE blah.id IN (' || in_values ||');'
INTO good;
IF(good = 1) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$sf$ LANGUAGE plpgsql;
Or, it may be easier given whatever your situation to simply use the
array as the argument to a row-wise AND or SOME expression.
--
erik jones <erik@myemma.com>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly |
|
| Back to top |
|
|
Erik Jones
*nix forums beginner
Joined: 12 May 2006
Posts: 10
|
| Posted: Tue Jul 18, 2006 6:36 pm Post subject:
Re: How to pass array of values to a pgplsql function
|
|
|
Erik Jones wrote:
| Quote: | Curtis Scheer wrote:
Does anyone have any examples of how I would make a stored procedure in
plpgsql that would allow for passing a list or arrays of values to be
used
in an sql IN clause? Like so: select * from table where field1 in
(values).
Is this possible?
Well, a good thing to note here is that there is a very distinct
semantic difference between an array in postgres and what IN clauses
take as input: and array
is a data type whereas IN clauses take a parenthesized list of comma
separated values. So, if you pass an array into a function wherein
you then need
to use those values in an IN clause, you can build yourself an string
of the values in the array, comma separated of course.
e.g.
CREATE OR REPLACE FUNCTION somefunc(ids int[]) RETURNS boolean AS $sf$
DECLARE
in_values varchar;
good int;
BEGIN
FOR i IN array_upper(ids, 1) LOOP
in_values := in_values || ids[i] || ',';
END LOOP;
in_values := substring(in_values FROM 1 FOR
character_length(in_values) - 1); -- this will chop off the last comma
EXECUTE 'SELECT 1 FROM blah WHERE blah.id IN (' || in_values ||');'
INTO good;
IF(good = 1) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$sf$ LANGUAGE plpgsql;
Or, it may be easier given whatever your situation to simply use the
array as the argument to a row-wise AND or SOME expression.
Whoa, replied to this out of the General mailing list before I saw the |
other answers on the SQL list... Sorry guys
--
erik jones <erik@myemma.com>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match |
|
| Back to top |
|
|
Curtis Scheer
*nix forums beginner
Joined: 18 Jul 2006
Posts: 2
|
| Posted: Tue Jul 18, 2006 6:42 pm Post subject:
Re: How to pass array of values to a pgplsql function
|
|
|
Thanks, Erik another possible solution would also be this.
Here it is in case you are interested.
On 7/18/06, Tony Wasson <ajwasson@gmail.com> wrote:
| Quote: | On 7/18/06, Curtis Scheer <Curtis@daycos.com> wrote:
Does anyone have any examples of how I would make a stored procedure
in plpgsql that would allow for passing a list or arrays of values
to be used in an sql IN clause? Like so: select * from table where
field1 in (values).
|
Ahhh... Here's an example using Tom's recommended field=ANY (arrayvalue)
SQL.
CREATE TABLE ids
(
id INTEGER
, PRIMARY KEY (id)
);
INSERT INTO ids VALUES (1);
INSERT INTO ids VALUES (2);
INSERT INTO ids VALUES (3);
CREATE OR REPLACE FUNCTION example_array_input(INT[]) RETURNS SETOF ids AS
$BODY$ DECLARE
in_clause ALIAS FOR $1;
clause TEXT;
rec RECORD;
BEGIN
FOR rec IN SELECT id FROM ids WHERE id = ANY(in_clause)
LOOP
RETURN NEXT rec;
END LOOP;
-- final return
RETURN;
END
$BODY$ language plpgsql;
SELECT * FROM example_array_input('{1,2,4,5,6}'::INT[]);
---------------------------(end of broadcast)---------------------------
| Quote: | Well, a good thing to note here is that there is a very distinct
semantic difference between an array in postgres and what IN clauses
take as input: and array
is a data type whereas IN clauses take a parenthesized list of comma
separated values. So, if you pass an array into a function wherein
you then need
to use those values in an IN clause, you can build yourself an string of
the values in the array, comma separated of course.
|
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 7:32 am | All times are GMT
|
|
Books | Free website & online homepage | Homeowner Loans | Debt Consolidation | Credit Counseling
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
