I have a populated table that already has some specific columns. I'd like to alter the table structure by adding a new column which will represent a field that is the count of the rows of another table.
Is it possible to implement it through a trigger in order to do it automatically after the alter command? I have come up with something like this but apparently doesn't update the column:
Function for the trigger:
CREATE FUNCTION update_column() RETURNS TRIGGER AS
$BODY$
DECLARE
num INTEGER;
BEGIN
SELECT COUNT(*) INTO num FROM mytable1, mytable2 WHERE mytable1.field = mytable2.field GROUP BY mytable1.field;
UPDATE mytable1 SET new_column = num;
END;
$BODY$
LANGUAGE PLPGSQL;
The trigger:
CREATE TRIGGER insert
AFTER UPDATE
ON mytable1
FOR EACH ROW
EXECUTE PROCEDURE update_column();
And the alter command:
ALTER TABLE mytable1 ADD new_column INT;
Is it possible to implement it through a trigger in order to do it automatically after the alter command?
Firstly, your trigger fires after each update on the table, not after the alter table. Second, there would not be much point for a trigger on such DDL event anyway.
If your table has data already, you might need to initialize the column after its creation, with something like:
update mytable1 t1
set new_column = (select count(*) from mytable2 t2 where t2.field = t1.field)
As for the logic you wanted in the trigger: you need to use new and old to refer to the row that was changed in the original table - and, typically, you would expect the update to affect either no rows (if field was not changed), or two rows (if it was):
update mytable1 t1
set new_column = (select count(*) from mytable2 t2 where t2.field = t1.field)
where t1.field in (new.field, old.field) and new.field <> old.field
That said, I would not recommend such set up. Maintaining such derived information is expensive and tedious (you have an update trigger, now you need an insert and a delete trigger too). Instead, you can compute the logic on the fly when needed, or create a view:
create view myview as
select t1.*,
(select count(*) from mytable2 t2 where t2.field = t1.field) as new_column
from mytable1 t1