PL/pgSQL triggers with parameters don't work

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: PL/pgSQL triggers with parameters don't work
Date: 2001-07-11 16:53:38
Message-ID: 200107111653.f6BGrcc84584@hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Jos Mara Fernndez Gonzlez (jmfernandez(at)cnb(dot)uam(dot)es) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
PL/pgSQL triggers with parameters don't work

Long Description
The report is for PostgreSQL 7.1.2. When you create a trigger which calls a function with parameters, the system links against the function with the same name and no parameter, and if it doesn't exist it tells "function doesn't exist". Tests have been made with PL/pgSQL-encoded functions.

Sample Code
CREATE table a(
b INTEGER,
c TEXT
);

CREATE FUNCTION cascaya (INTEGER) RETURNS OPAQUE AS '
DECLARE
value ALIAS FOR $1;
BEGIN
IF value > 0
THEN
RAISE EXCEPTION ''Value: %'',value;
END IF;
return NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER case1 BEFORE INSERT
ON a FOR EACH ROW
EXECUTE PROCEDURE cascaya(5);

ERROR: CreateTrigger: function cascaya() does not exist

CREATE FUNCTION cascaya () RETURNS OPAQUE AS '
BEGIN
return NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER case2 BEFORE INSERT
ON a FOR EACH ROW
EXECUTE PROCEDURE cascaya(5);

select proname,pronargs,oid from pg_proc where proname='cascaya';

proname | pronargs | oid
---------+----------+---------
cascaya | 0 | 1050555
cascaya | 1 | 1050554

select tgname,tgfoid,tgnargs from pg_trigger where tgname='case2';

tgname | tgfoid | tgnargs
--------+---------+---------
case2 | 1050555 | 1

No file was uploaded with this report

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2001-07-11 17:30:51 Re: ecpg: INITALLY DEFERRED translated into intially deferrable
Previous Message Tom Lane 2001-07-11 16:40:27 Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found