(Preface: this may be directed to the wrong newsgroup; if so, don't hesitate
to let me know.)
I'm fairly new to postgresql (and databases in general); I understand the
basic concepts behind tables and relations and, to some extent, query
optimization, but I have something of an interesting problem in one of my
projects.
I have a table of users for a website, and a table of groups for those users
to be in. Membership in a group is tracked in a third table, which simply
refers to the user and group tables. What I would like to do is have
postgresql automatically insert a row into the membership table when a new
row is inserted into the 'user' table that makes that new user a member of a
group called 'Everyone'.
I assume the correct way to do this is through a trigger on the user table
(AFTER INSERT) but have no experience whatsoever writing triggers. I'm not
necessarily asking for someone to write it for me, although that would be
most kind, but some resources on how to write it myself would be just as
good.
Regards and thanks,
Owen Jacobson
(ojacobson at mx hyphen deus dot net)
Tables:
CREATE SEQUENCE uidseq START 0 MINVALUE 0;
CREATE TABLE users (
UID BIGINT
PRIMARY KEY
DEFAULT nextval('uidseq'),
Name VARCHAR(80)
UNIQUE
NOT NULL,
...other columns, irrelevant...
);
CREATE SEQUENCE gidseq START 0 MINVALUE 0;
CREATE TABLE groups (
GID BIGINT
PRIMARY KEY
DEFAULT nextval('gidseq'),
Name VARCHAR(80)
UNIQUE
NOT NULL
);
CREATE TABLE members (
UID BIGINT
NOT NULL
REFERENCES users (UID)
ON UPDATE CASCADE
ON DELETE CASCADE,
GID BIGINT
NOT NULL
REFERENCES groups (GID)
ON UPDATE CASCADE
ON DELETE CASCADE
);
