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@xxxxxxxxxxxxx
1684 Nightingale Avenue Suite 201
Sunnyvale, CA 94087 408-733-2737(fax)
http://www.zeemaps.com
