Re: [OT] postgresql triggers

[will trillich <will@serensoft.com>]

On Sat, Mar 03, 2001 at 11:50:03PM -0500, Ron Peterson wrote:
> will trillich wrote:
> 
> <snip>
> 
> > when i expand this and try to add a SELECT on other tables, or an
> > UPDATE to another table or two, postgresql gripes about
> > 
> >         NEW used in non-RULE query...
> 
> Could you provide an example of what *doesn't* work?

i think i've got it! mwaah ha ha...

i follow the example from the postgresql-doc/html/user/c4*40.html
document and define a function to be called via TRIGGER...

	CREATE FUNCTION easy() RETURNS OPAQUE AS '
	DECLARE
		x CHAR(1) := NEW.somefield FROM 1 FOR 1;
	BEGIN
		-- whatever, yada yada, bada bing bada boom
		RETURN NEW;
	END;
	' language 'plpgsql';

and this results in "ERROR: use of NEW in non-rule procedure"
when actually called via TRIGGER. i change it instead to

	CREATE FUNCTION easy() RETURNS OPAQUE AS '
	DECLARE
		x CHAR(1); -- no := assignment here
	BEGIN
		x := NEW.somefield FROM 1 FOR 1;
		-- whatever, yada yada, bada bing bada boom
		RETURN NEW;
	END;
	' language 'plpgsql';

and suddenly all is right with the world. so i can't refer to the
NEW (or probably OLD) pseudotables WITHIN THE DECLARE section.
apparently.

Q: is this a bug? is this a feature?

-- 
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
		-- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!