stored function - array parameter - how many element in array ?

On 7/13/06, Özgür Tuğrul <ozgur.tugrul@gmail.com> wrote:

hello,

the question is very clear .. when we write stored function, we can use
array parameter as a variable .. but, in the function, how do i know how
many element in that array ?

i want to perform some dml operations about each one like (delete, update or
delete)

can anyone show me the example or tell me the function name i should use ..

have you looked at array_upper/array_dims? you can see an example of
array_upper here:
http://people.planetpostgresql.org/merlin/index.php?/archives/4-fun-with-arrays.html

(shameless plug) :)

merlin

Have you considered using a set instead? We had a similar need and
were using an array as a parameter. That turned out to be taking too
long. Recently we have changed it to a set and it seems to work
faster, although I must admit I haven't timed it yet. In the first
case you call it by "select deleteEntries(1, '{1, 2, 3}'));" and in
the second, "select deleteEntries(1, '(1, 2, 3)');"

The first one was defined as:

CREATE OR REPLACE FUNCTION deleteEntries(int, integer[])
RETURNS Void
AS
'
DECLARE
G ALIAS FOR $1;
Entries ALIAS FOR $2;
ThisEntryId Integer;
BEGIN
IF array_lower(Entries, 1) is NULL THEN
RETURN ''True'';
END IF;
FOR I IN array_lower(Entries, 1)..array_upper(Entries, 1) LOOP
ThisEntryId := Entries[i];
DELETE FROM Details WHERE id = ThisEntryId;
END LOOP;
RETURN;
END;
'
LANGUAGE plpgsql
STABLE
RETURNS NULL ON NULL INPUT;

The new one is defined as:

CREATE OR REPLACE FUNCTION deleteEntries(int, varchar)
RETURNS Void
AS
'
DECLARE
G ALIAS FOR $1;
Entries ALIAS FOR $2;
BEGIN
EXECUTE ''DELETE FROM Details WHERE id IN '' || Entries;
RETURN;
END;
'
LANGUAGE plpgsql
STABLE
RETURNS NULL ON NULL INPUT;

On Jul 13, 2006, at 5:38 AM, Özgür Tuğrul wrote:

hello,

the question is very clear .. when we write stored function, we can
use array parameter as a variable .. but, in the function, how do
i know how many element in that array ?

i want to perform some dml operations about each one like (delete,
update or delete)

can anyone show me the example or tell me the function name i
should use ..

regards
----------------------------------------------------------------------
-----------------------------------------
There is no e-mail anymore .. There is Gmail :)

--
Claire McLister mclister@zeesource.net
1684 Nightingale Avenue Suite 201
Sunnyvale, CA 94087 408-733-2737(fax)

http://www.zeemaps.com

On Jul 13 08:28, Claire McLister wrote:

Have you considered using a set instead? We had a similar need and
were using an array as a parameter. That turned out to be taking too
long. Recently we have changed it to a set and it seems to work
faster, although I must admit I haven't timed it yet.

Did you experience same results when you use an indexable (integer)
array type supplied by intarray contrib module? Furthermore, there're
lots of useful procedures and operators which supplies any kind of
functionality you'll ever need with arrays. Also it's obviouse that
there's no need to tell that these libraries are written in C with quite
optimized algorithms.

Regards.