Thread: Insert with pl/pgsql trigger

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 nullmac          | 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

"Woody Woodring" <george.woodring@iglass.net> writes:
> 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;

That's never going to work because of quoting issues, and it wouldn't be
an efficient way if it did work (because of having to re-parse and
re-plan the INSERT each time).  And if it did act the way you are
imagining, it still wouldn't be a good way because you typically want
some additional columns in the log table, such as a timestamp.

In recent releases you can do it like this:
INSERT INTO cpe_health_history VALUES (NEW.*);

which can be extended to, eg,
INSERT INTO cpe_health_history VALUES (NEW.*, now());
        regards, tom lane