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

Trouble with plpgsql generic trigger function using special variables

From: "Lenorovitz, Joel" <Joel(dot)Lenorovitz(at)usap(dot)gov>
To: <pgsql-novice(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org>
Subject: Trouble with plpgsql generic trigger function using special variables
Date: 2006-10-31 20:20:48
Message-ID: 7119BB016BDF6445B20A4B9F14F50B2D44A8AC@WILSON.usap.gov (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
I'd like to create a trigger function whose use can extend to multiple
tables by employing the special variables available (e.g., TG_RELNAME).
Below is a simple version of such a function that ought to prevent
insertion of greater than 4 total records in the table that calls it.
I'm not sure that I'm using or dereferencing the trigger variables
correctly, however, particularly in the query.  I have tried many
syntax, type casting, and alternate variable assignment variations, but,
aside from parsing successfully, this code does not seem to work as
intended.    Can somebody correct this specific example to have it work
properly and/or further explain how to use these variables?  Any advice
on outputting the values of the variables to the console for inspection
during testing would be welcome as well (RAISE EXCEPTION doesn't allow a
variable value in the message string, plus it seems a little harsh).

Thanks,
JL

CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$
	BEGIN
		IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN
			IF (SELECT COUNT(*) FROM text(TG_RELNAME)) < 4
THEN
				RETURN NEW;
			ELSE
				RETURN NULL;
			END IF;
		END IF;
	END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_bi BEFORE INSERT ON test
	FOR EACH ROW EXECUTE PROCEDURE trigger_fxn();

Responses

pgsql-novice by date

Next:From: Richard HuxtonDate: 2006-11-01 08:32:50
Subject: Re: Trouble with plpgsql generic trigger function using
Previous:From: Tom LaneDate: 2006-10-31 03:51:59
Subject: Re: GRANT SELECT ON table TO $1

pgsql-general by date

Next:From: Ron JohnsonDate: 2006-10-31 20:34:10
Subject: Re: RAM Based Disk Drive?
Previous:From: Andrew SullivanDate: 2006-10-31 20:19:03
Subject: Re: Pgsql on Solaris

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