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
forthe 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
