Re: select for update

On Wed, 20 Nov 2002, Justin Georgeson wrote:

> I'm pretty new to databases in general, and would like to find a spiffy
> way to do something. I want to use two columns from one table to
> populate three columns in two other tables. Assuming t1 is the table I
> want to take the values from, here is the structure of what I want to
> insert into t2 and t3.
>
> t2.id = t1.id
> t2.groupname = t1.username
> t2.owner = t1.username
>
> t3.id = <next available>
> t3.groupid = t1.id
> t3.username = t1.username
> t3.writeperms = 31

If you're trying to populate the entire table and t3.id is a serial, I
think you could do:

insert into t2 (id, groupname, owner) select id, groupname, username from
t1;
insert into t3 (groupid, username, writeperms) select id, username, 31
from t1;

If you mean that on inserts to t1 you want to make rows in the other
tables then you probably want a simple trigger function like (untested):

create function t1_make_t2_and_t3() returns OPAQUE as '
BEGIN
INSERT INTO t2 (id, groupname, owner) values (NEW.id, NEW.username,
NEW.username);
INSERT INTO t3 (groupid, username, writeperms) values (NEW.id,
NEW.username, 31);
return NEW;
END;' language 'plpgsql';
create trigger t1_make_t2_and_t3_trig after insert on t1 for each row
execute procedure t1_make_t2_and_t3();