Thread: serious issues with TG_OP

serious issues with TG_OP

From
Michael Zouroudis
Date:
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



Re: serious issues with TG_OP

From
"Dan Langille"
Date:
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