Re: mixed insert... ?

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(at)yahoo(dot)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/