By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,378 Members | 3,020 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,378 IT Pros & Developers. It's quick & easy.

trigger function to replace insert with update

P: 3
This is a really simple question and I'v been working on it for 2 hours........

The table is:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE download
  2. (
  3.   id integer NOT NULL DEFAULT nextval('seq'::regclass),
  4.   songid integer NOT NULL,
  5.   counter integer NOT NULL,
  6.   weeknumber integer NOT NULL,
  7.   CONSTRAINT download_pkey PRIMARY KEY (id ),
  8.   CONSTRAINT download_songid_fkey FOREIGN KEY (songid)
  9.       REFERENCES song (id) MATCH SIMPLE
  10.       ON UPDATE NO ACTION ON DELETE NO ACTION
  11. )
  12. WITH (
  13.   OIDS=FALSE
  14. );
  15. ALTER TABLE download
  16.   OWNER TO postgres;
  17.  
and what I want to do is whenever I want to insert a new record ,if the combination of songid and weeknumber is exist the counter add one otherwise execute the insert.
The trigger function I make is:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION download_counter()
  2.   RETURNS trigger AS
  3. $BODY$
  4. DECLARE
  5.  tmpid INTEGER;
  6. BEGIN
  7. SELECT id INTO tmpid FROM download WHERE songid=NEW.songid AND weeknumber=NEW.weeknumber;
  8. IF NOT FOUND THEN
  9. RETURN NEW;
  10. ELSE
  11. UPDATE download SET counter=counter+1 WHERE id=NEW.id;
  12. RETURN NEW;
  13. END IF;
  14. END;
  15. $BODY$
  16.   LANGUAGE plpgsql VOLATILE
  17.   COST 100;
  18. ALTER FUNCTION download_counter()
  19.   OWNER TO postgres;
  20.  
And below is my trigger:
Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER update_or_insert
  2.   BEFORE INSERT
  3.   ON download
  4.   FOR EACH ROW
  5.   EXECUTE PROCEDURE download_counter();
  6.  
Since I cannot find replace in postgresql ,I think maybe I misused the return type.
Thanks very much for anyone who can give me some advices.
Dec 13 '11 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
You can also use writeable CTE if you use postgres 9.1
See here
http://vibhorkumar.wordpress.com/201...ostgresql-9-1/
Dec 14 '11 #2

P: 3
Thanks for your reply.
I would like to integrate the sql into my code,such as:

insert into download (weeknumber, singerid, counter) values ((SELECT EXTRACT(WEEK FROM (SELECT CURRENT_TIMESTAMP))), ?, 1)

so, I can do it with sql on the console, but I have no idea how to integrate the "WITH upsert as" statement into my code.
Dec 14 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.