Обсуждение: 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.