Insert with pl/pgsql trigger

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

Responses

Browse pgsql-sql by date

  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