From: | "Woody Woodring" <george(dot)woodring(at)iglass(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Insert with pl/pgsql trigger |
Date: | 2008-05-07 14:36:26 |
Message-ID: | 003101c8b04f$bab777b0$80b1a8c0@istructure.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-05-07 14:38:17 | Re: how to check if a point is contained in a polygon ? |
Previous Message | Achilleas Mantzios | 2008-05-07 11:52:59 | Re: dbmirror - migration to 8.3 from 7.4 |