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

                      http://www.zeemaps.com