Обсуждение: Stored Procedure Problem

I set up my database to include lang plpgsql.
When trying to create this simple stored procedure and
trigger below, the procedure is created but the
trigger fails.
the error returned is

"psql:importPageDataFunction:25: ERROR:
CreateTrigger: function pagedata_insert() does not
exist".

 CREATE FUNCTION pagedata_insert
(integer,integer,integer,integer,integer,integer,integer)
RETURNS opaque AS '

    DECLARE
        g ALIAS FOR $1;
        z ALIAS FOR $2;
        t ALIAS FOR $3;
        p ALIAS FOR $4;
        r ALIAS FOR $5;
        s ALIAS FOR $6;
        temp ALIAS FOR $7;
    BEGIN
        IF tempid IS NULL THEN
            tempid := (SELECT template FROM signs WHERE id =
sid);
        END IF;

        INSERT INTO pagedata (gid, zid, tid, page, rid, sid,
tempid) VALUES (g, z, t, p, r, s, temp);

        RETURN NULL;
    END;

' LANGUAGE 'plpgsql';

CREATE TRIGGER insertpagedata BEFORE INSERT
    ON pagedata FOR EACH ROW EXECUTE
PROCEDURE pagedata_insert (gid, zid, tid, page, rid,
sid, tempid);

I have notice that the procedure is in pg_proc, but if
you refer to it with "Drop Function pagedata_insert"
it won't find it either.
If I take out the argument declaration both objects
are created successfully.

thanks in advance

Mike Marconi

__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com

Mike Marconi wrote:
> [...]
>
> I have notice that the procedure is in pg_proc, but if
> you refer to it with "Drop Function pagedata_insert"
> it won't find it either.
> If I take out the argument declaration both objects
> are created successfully.

    You  have  to  match  the  functions  argument  list  in DROP
    FUNCTION to actually hit it. There could  be  any  number  of
    functions  named  the same, as long as their arguments differ
    (function overloading).

    Trigger procedures don't  take  arguments.  So  you  have  to
    CREATE  it  with  empty  parens. The arguments you specify in
    CREATE TRIGGER will be found in the array TG_ARGV[] then.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #