Re: create triggers.

On Sun, 2003-10-19 at 22:51, Ling Xiaoyu wrote:
> Hello there.
> Can anybody help me with Postgresql triggers?
> what I need is a trigger which update value of field
> "tables_rows.total_rows" to rows count of table
> "zzz" if I insert new row in table "zzz"...
>
> CREATE TABLE zzz (
> ...
> ...
> );
> CREATE TABLE tables_rows (
> table_name VARCHAR(32),
> total_rows BIGINT,
> CONSTRAINT pk_total_rows PRIMARY KEY(table_name,
> total_rows)
> );

CREATE OR REPLACE FUNCTION rows_count_trigger()
RETURNS VOID
AS '
BEGIN
IF TG_OP = ''INSERT'' THEN
UPDATE tables_rows
SET total_rows = total_rows + 1
WHERE table_name = TG_RELNAME;
ELSIF TG_OP = ''DELETE'' THEN
UPDATE tables_rows
SET total_rows = total_rows - 1
WHERE table_name = TG_RELNAME;
END;
RETURN NULL;
END;'
LANGUAGE plpgsql;

BEGIN;

-- Make sure no rows can be added to zzz until we have finished
LOCK TABLE zzz IN SHARE ROW EXCLUSIVE MODE;

CREATE TRIGGER zzz_rows_count
AFTER INSERT OR DELETE ON zzz
FOR EACH ROW EXECUTE PROCEDURE rows_count_trigger();

-- Initialise the row count record
DELETE FROM tables_rows WHERE table_name = 'zzz';

INSERT INTO tables_rows (table_name, total_rows)
VALUES ('zzz', (SELECT COUNT(*) FROM zzz));

COMMIT;

-- Now ready for normal operations on zzz

This trigger makes tables_rows a bottleneck in your system, of course.
If you commonly do things that add or delete a lot of rows in zzz and
zzz is not too big, it might possibly be more efficient to do a trigger
that counts zzz but only runs for each statement.
(Not possible before 7.4).

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For the LORD God is a sun and shield; the LORD will
give grace and glory; no good thing will he withhold
from them that walk uprightly." Psalms 84:11