Обсуждение: 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/