Thread: OLD.oid issues...

OLD.oid issues...

From
"Rod Taylor"
Date:
Doesn't appear that you can use OLD.oid in triggers (actually does more, =
but thats good enough):

CREATE FUNCTION history_update_delete() RETURNS OPAQUE AS '
DECLARE
    v_tablename varchar(40);
    v_history_tablename varchar(48);
    v_operation varchar(6);
BEGIN
    v_operation :=3D TG_OP;
    v_tablename :=3D TG_RELNAME;
    v_history_tablename :=3D ''history_'' || v_tablename;

    INSERT INTO v_history_tablename SELECT ''v_operation'' as =
history_change_type
        , OLD.oid as orignal_oid
        , *
        FROM v_tablename
        WHERE oid =3D OLD.oid;

    RETURN NEW;

END;
' LANGUAGE 'plpgsql';
--
ERROR:  record old has no field oid
 

The below also fails.

create table example (
  original_oid oid REFERENCES table(oid)
        ON UPDATE CASCADE
        ON DELETE SET NULL
);
--
ERROR:  UNIQUE constraint matching given keys for reference table "table" not found

Postgresql 7.1beta3.  I'd consider these to be bugs myself but I've not =
tried them in previous versions to know if it's really just a new =
feature :)
--
Rod Taylor
 
There are always four sides to every story: your side, their side, the truth, and what really happened.
Attachment

Re: OLD.oid issues...

From
Stephan Szabo
Date:
On Thu, 18 Jan 2001, Rod Taylor wrote:

> create table example (
>   original_oid oid REFERENCES table(oid)
>         ON UPDATE CASCADE
>         ON DELETE SET NULL
> );
> --
> ERROR:  UNIQUE constraint matching given keys for reference table "table" not found
> 
> Postgresql 7.1beta3.  I'd consider these to be bugs myself but I've not =
> tried them in previous versions to know if it's really just a new =
> feature :)

Actually I know the latter never really should have worked in past
versions.  It may have let you define it before, but I believe it would
have errored on the actual fk constraint when used, now it just won't let
you define it. I think referencing oids is on the todo list (although
you have to give ref actions like the ones you have or the constraint is
pretty ugly).