serious issues with TG_OP
serious issues with TG_OP
От:
Michael Zouroudis <mzouroudis@idealcorp.com>
Дата:
I'm once again having a problem with postgres and would greatly
appreicate any help. there are two tables that i'm working with, TEST
and ONE, created here;
CREATE TABLE test (
test_id serial primary key,
fake text,
below text,
above text,
behind text
);
CREATE TABLE one(
last_id serial primary key,
first text,
begin text,
hope text
);
and here's my function;
-----------------------------------------------------------------------------------------
CREATE FUNCTION pearl() RETURNS OPAQUE AS '
DECLARE
t test%ROWTYPE;
o one%ROWTYPE;
x text;
y text;
z text;
a text;
xx text;
yy text;
zz text;
aa text;
BEGIN
x := new.fake;
y := new.below;
z := new.above;
a := new.behind;
xx := x;
yy := y;
zz := z;
--insert into test table
--create function that inserts into table ONE automatically when an
insert into TEST happens
if TG_OP = ''INSERT'' then
raise notice ''INSERT xx is equal to % '', x;
INSERT INTO one (first, begin, hope) VALUES ( xx, yy, zz);
end if;
--update data in TEST
--create function that will automatically update data in ONE after
update in TEST
if TG_OP = ''UPDATE'' then
raise notice ''UPDATE xx is equal to % '', x;
UPDATE one SET first = x, begin =''yy'', hope = zz WHERE
begin = ''eed'';
end if;
--Delete data from TEST table.
--create function that will automatically delete data in ONE after
delete in TEST
if TG_OP = ''DELETE'' then
raise notice ''DELETE xx is equal to % '', x;
DELETE FROM one WHERE first = x;
end if;
RETURN NULL;
END;
'language 'plpgsql';
CREATE TRIGGER love AFTER INSERT or UPDATE or DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE pearl();
----------------------------------------------------------------------------
when i run it with an insert then update and then a delete statement in a script, i get the following message
----------------------------------------------------------------------------
psql:script:9: NOTICE: INSERT xx is equal to ddd
INSERT 215042 1
psql:script:13: NOTICE: UPDATE xx is equal to hello
UPDATE 1
psql:script:17: NOTICE: Error occurred while executing PL/pgSQL function pearl
psql:script:17: NOTICE: line 21 at assignment
psql:script:17: ERROR: record new is unassigned yet
-------------------------------------------------------------------------------
the function is printing out a value for x (hello), but not deleting it. i am lost on how to correctly assign a value for the delete. Please somebody help me before i do "kill -9" the postmaster and then blow up my computer.
thanks in advance,
mike
--
Mike Zouroudis
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14
Re: serious issues with TG_OP
От:
"Dan Langille" <dan@langille.org>
Дата:
On 23 Aug 2002 at 0:41, Michael Zouroudis wrote: > CREATE FUNCTION pearl() RETURNS OPAQUE AS ' > > DECLARE > > t test%ROWTYPE; > o one%ROWTYPE; > x text; > y text; > z text; > a text; > xx text; > yy text; > zz text; > aa text; > > BEGIN > > > x := new.fake; > y := new.below; > z := new.above; > a := new.behind; Your problem is in the above lines. My bet: NEW will not exist upon deletes. > psql:script:9: NOTICE: INSERT xx is equal to ddd > INSERT 215042 1 > psql:script:13: NOTICE: UPDATE xx is equal to hello > UPDATE 1 > psql:script:17: NOTICE: Error occurred while executing PL/pgSQL function pearl > psql:script:17: NOTICE: line 21 at assignment > psql:script:17: ERROR: record new is unassigned yet > ------------------------------------------------------------------------------- > > the function is printing out a value for x (hello), but not deleting it. > i am lost on how to correctly assign a value for the delete. Do you know now? -- Dan Langille I'm looking for a computer job: http://www.freebsddiary.org/dan_langille.php