Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group