By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

create triggers.

P: n/a
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"...

CAREATE TABLE zzz (
...
...
);
CREATE TABLE tables_rows (
table_name VARCHAR(32),
total_rows BIGINT,
CONSTRAINT pk_total_rows PRIMARY KEY(table_name,
total_rows)
);

Thanks a lots
regars
Ling.

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 Ol************@lfix.co.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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.