Thread: OLD.oid issues...
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';
--
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
Rod Taylor
There are always four sides to every story: your side, their side, the truth, and what really happened.
Attachment
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).