From: | tomasz konefal <twkonefal(at)yahoo(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | mixed insert... ? |
Date: | 2002-01-14 17:22:29 |
Message-ID: | 20020114172229.58136.qmail@web12004.mail.yahoo.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
hello list,
i'm hoping someone can enlighten me on the best way to do an insert. i am
creating a little user database that looks something like this:
CREATE TABLE ftpclients (
clientid serial primary key,
foldernumber int,
loginname varchar(32),
createdate date,
activedate date,
expiredate date,
quota int,
active boolean,
billbydate boolean,
passwd varchar(32)
);
and i'd like to log any changes to the ftpclients table (above) in a second
table like this:
CREATE TABLE clhistory (
clientid int,
entry text,
moddate date,
FOREIGN KEY (clientid) REFERENCES ftpclients
);
i'm writing a script in perl that will fetch the appropriate data and perform
these inserts, but i'm having trouble inserting into the clhistory table
because i need to insert two fields from the ftpclients table (clientid, and
createdate), but also a third value that comes from the script that runs the
query (so i can't use:
INSERT into ... SELECT...;
directly). right now, i see my options as (some perl'isms):
1 - $result=SELECT clientid from ftpclients WHERE
loginname=$client{loginname};
INSERT INTO clhistory VALUES ($result, $entry, $createdate);
2 - store the most recent comment in the ftpclients table, which would allow
an
INSERT ... SELECT;
ideally, i'd like to insert into the clhistory table a comment with a
clientid value that matches the desired loginname from the ftpclients table.
am i on the right track, or is there an entirely better way to handle this?
thanks,
twkonefal
______________________________________________________________________
Web-hosting solutions for home and business! http://website.yahoo.ca
From | Date | Subject | |
---|---|---|---|
Next Message | Paulo J. Matos | 2002-01-14 17:28:54 | User creation of DB |
Previous Message | Mourad EL HADJ MIMOUNE | 2002-01-14 16:59:16 | Multilanguage support |