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

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(at)zeesource(dot)net
1684 Nightingale Avenue Suite 201
Sunnyvale, CA 94087 408-733-2737(fax)

http://www.zeemaps.com