relation “old” does not exist

问题:

When i try execute this script, i get an error: ERROR: relation "old" does not exist. Script:

update organization 
set row_status = 30;

I found some question like this, but their dont help me. My trigger:

Create OR REPLACE function PMigrateDataToHistory()
returns trigger as $PMigrateDataToHistory$
begin

insert into organization_history
select 
  * 
from 
  OLD;

delete 
from 
  organization as org USING 
  OLD as o
where 
  o.id = org.id and
  o.version = org.version;

insert into organization
select
  n.id,
  n.created_at,
  n.updated_at,
  n.row_status,
  n.version + 1,
  n.resource
from
  NEW as n;
end;

$PMigrateDataToHistory$ LANGUAGE plpgsql;

CREATE TRIGGER TRMigrateDataToHistory AFTER update or delete ON organization
    FOR EACH ROW EXECUTE PROCEDURE PMigrateDataToHistory();

回答1:

For the first INSERT, use something like

INSERT INTO organization_history VALUES (OLD.*);

The DELETE and the second INSERT are ill-conceived – for one, this will cause a lot of unnecessary churn in the organization table.

It would be much better to use a BEFORE trigger, add 1 to NEW.version and return NEW. This would cause the values to be adjusted before the record is written to the table.

回答2:

You are mis-understanding what OLD and NEW are: those aren't tables, it is the row that has been inserted or modified.

However you do not need to delete and insert everything. Just increment the version in a BEFORE trigger.

Then you can simplify your trigger to:

create or replace function pmigratedatatohistory()
returns trigger as $$
BEGIN
  insert into organization_history values (old.*);
  new.version := new.version + 1; -- increment the version
  return new;
END;
$$ 
LANGUAGE plpgsql;

You need a BEFORE trigger for that:

create trigger trmigratedatatohistory BEFORE update or delete ON organization
    for each row execute procedure pmigratedatatohistory();

---

The process when you delete a row doesn't make sense to me. The current code does the following:

1. A delete from organization is executed and the row is deleted
2. The trigger makes a copy of that row
3. The trigger tries to delete that row again (and does nothing, because it's an after trigger, so the row is already gone from the table)
4. The rigger re-inserts the row that should have been deleted with a higher version number - essentially reverting the delete completely.

The same behaviour can be achieved by the simply incrementing the version in the before trigger. If you want to prevent deletion completely simply return null in the before trigger:

create or replace function pmigratedatatohistory()
returns trigger as $$
BEGIN
  insert into organization_history values (old.*);
  new.version := new.version + 1; -- increment the version
  if TG_OP = 'DELETE' then 
    return null; -- "cancel" the delete 
  else
    return new;
  endif;
END;
$$ 
LANGUAGE plpgsql;