SQL postgresql insert statement

Question

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

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
– mu is too short, Commented May 10, 2013 at 4:07
I just required to write it for college... If it is 'C' for example it would be 'French-Speaking' — Andrei Ivanov
– Andrei Ivanov, Commented May 10, 2013 at 4:09

hd1 · Accepted Answer · 2013-05-10 04:15:50Z

1

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();

edited May 10, 2013 at 4:15

answered May 10, 2013 at 3:59

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrei Ivanov Over a year ago

Thanks, but I want to do it automatically - without having to run UPDATE

hd1 Over a year ago

See my edit for a revised answer, given your comment, @AndreiIvanov

Andrei Ivanov Over a year ago

There is few changes I had to make - but it worked! Thanks a lot!

Craig Ringer · Accepted Answer · 2013-05-10 04:11:03Z

0

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.

answered May 10, 2013 at 4:11

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Collectives™ on Stack Overflow

SQL postgresql insert statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related