Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a following question, for example I have a following table:

CREATE TABLE "regions" (gid serial PRIMARY KEY,
"__gid" int8,
"name" varchar(20),
"language" varchar(7),
"population" int8);

And I want to insert some records, say one of the values for "name" is - 'B', what sort of code would I have to write to change 'B' to 'English-Speaking'? Is that done with some sort of trigger? So would I have to write a trigger to change the values automatically on insert? Any help greatly appriciated!!!

share|improve this question
 
So you want to insert into regions (name, ...) values ('B', ...) and have it put 'English-Speaking' into name instead? Why do you want to do such a thing? –  mu is too short May 10 at 4:07
 
I just required to write it for college... If it is 'C' for example it would be 'French-Speaking' –  Andrei Ivanov May 10 at 4:09

2 Answers

up vote 1 down vote accepted

It's an UPDATE statement which will do what you wish, in this case:

UPDATE regions set name = 'English-Speaking' where name = 'B';

To put this in a function use something like:

CREATE OR REPLACE FUNCTION  insert_into_wgs()
RETURNS void AS
$$
BEGIN
UPDATE regions SET name = 'English-Speaking' WHERE name = 'B';
END
$$
LANGUAGE 'pgpsql';

Then you create a trigger to run this function:

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE
    insert_into_wgs();
share|improve this answer
 
Thanks, but I want to do it automatically - without having to run UPDATE –  Andrei Ivanov May 10 at 4:00
 
See my edit for a revised answer, given your comment, @AndreiIvanov –  hd1 May 10 at 4:16
 
There is few changes I had to make - but it worked! Thanks a lot! –  Andrei Ivanov May 10 at 5:41
up vote 0 down vote

Assuming I've guessed what you mean correctly from your description:

You will need a simple BEFORE INSERT OR UPDATE ... FOR EACH ROW EXECUTE trigger to invoke a PL/PgSQL trigger procedure that changes the value of the NEW record and then does a RETURN NEW.

The documentation contains abundant details, and since this is homework I'm not going to provide a complete example. Start with CREATE TRIGGER and PL/pgSQL trigger procedures.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.