I am trying to create a table that is a log of another table in 8.3.1 using
a trigger. Both tables look like
Table "public.cpe_health_history"
Column | Type | Modifiers
--------------+------------+-----------
cpe_healthid | integer | not null
mac | macaddr |
polldate | integer |
health | smallint |
rank | smallint |
data | integer[] |
alarm | smallint[] |
My trigger is :
CREATE OR REPLACE FUNCTION log_cpe_health() RETURNS trigger AS '
DECLARE
BEGIN
-- Update last outage before inserting
EXECUTE ''INSERT INTO cpe_health_history VALUES '' || NEW;
END;
' LANGUAGE plpgsql;
-- Create Trigger on outagelog table
CREATE TRIGGER cpe_health_log AFTER INSERT OR UPDATE ON dhct_health
FOR EACH ROW EXECUTE PROCEDURE log_cpe_health();
I am getting the following error:
health-test=> INSERT INTO dhct_health (mac, polldate, health, rank, data,
alarm) VALUES ('000000000001', 1210169492, 3, 9, '{2, 4,6}', '{3,3,3}');
ERROR: syntax error at or near ":"
LINE 1: INSERT INTO cpe_health_history VALUES (7,00:00:00:00:00:01,1...
^
QUERY: INSERT INTO cpe_health_history VALUES
(7,00:00:00:00:00:01,1210169492,3,9,"{2,4,6}","{3,3,3}")
CONTEXT: PL/pgSQL function "log_cpe_health" line 4 at EXECUTE statement
If I change the insert on the command line to (it works):
health-test=> INSERT INTO cpe_health_history VALUES
(7,'00:00:00:00:00:01',1210169492,3,9,'{2,4,6}','{3,3,3}');
INSERT 0 1
Am I using the "NEW" parameter wrong?
Thanks,
Woody
----------------------------------------
iGLASS Networks
3300 Green Level Rd. West
Cary NC 27519
(919) 387-3550 x813
www.iglass.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
