treacle=> CREATE TABLE colorcode
treacle-> (
treacle(> id int4 PRIMARY KEY,
treacle(> tagid int4,
treacle(> logicid int4,
treacle(> paramvalue float8
treacle(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "colorcode_pkey" for table "colorcode"
CREATE TABLE
treacle=>
treacle=> CREATE OR REPLACE FUNCTION xx() RETURNS trigger AS $$
treacle$> BEGIN
treacle$> if exists( select 1 from colorcode where id = NEW.ID ) then
treacle$> update colorcode set logicid = NEW.logicid, paramvalue = NEW.paramvalue where id = NEW.id;
treacle$> RETURN NULL;
treacle$> else
treacle$> RETURN NEW;
treacle$> end if;
treacle$> END;
treacle$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
treacle=>
treacle=> CREATE TRIGGER xxx BEFORE INSERT ON colorcode
treacle-> FOR EACH ROW EXECUTE PROCEDURE xx();
CREATE TRIGGER
treacle=>
treacle=> create sequence colorcode_seq;
CREATE SEQUENCE
treacle=> insert into colorcode values (nextval('colorcode_seq'),
INSERT 139334 1
treacle=> insert into colorcode values ( 1, 200, 2000, 3.14 );
INSERT 0 0
treacle=> select * from colorcode;
id | tagid | logicid | paramvalue
----+-------+---------+---
1 | 100 | 2000 | 3.14
(1 row)
Main Topics
Browse All Topics
by: earthman2Posted on 2006-09-02 at 01:00:18ID: 17441609
What you have specified ie FROM x ON DUPLICATE ... is not valid PostgreSQL syntax as I know it Jim. Where did you get that syntax from ?
There is work ongoing on a MERGE / REPLACE statement see http://momjian.us/mhonarc/
This might appear in version 8.2 (or it might not).
Using a trigger may work for you see http://www.experts-exchang