Thread: mixed insert... ?
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
You need to use a trigger, which will do the insert for you automatically. Some references: http://www.ca.postgresql.org/docs/aw_pgsql_book/node166.html http://www.postgresql.org/idocs/index.php?sql-createtrigger.html http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html --- tomasz konefal <twkonefal@yahoo.ca> wrote: > 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __________________________________________________ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/