Re: Storing number '001' ?

Chuck,

>   With these two tables, entering data gets a tad more complicated.

Yup.  Time to write a GUI.

> CREATE TABLE blast (
> blast_id SERIAL PRIMARY KEY,
> others..,
> score integer,
> homolog text
> );
>
>
> CREATE TABLE contig_blast (
> blast_id INTEGER REFERENCES blast(blast_id) ON DELETE CASCADE,
> contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE,
> UNIQUE(blast_id,contig_id)
> );
>
> w/ all contig data loaded into TABLE contig
>
> 1st: lookup contig_id for blast data to be entered.
> 2nd: INSERT INTO blast(col1..coln) VALUES(A...N)
> 3rd: after INSERT -> get blast_id for data just INSERTED
>     $blast_id = currval('blast_id')
>
> 4th: insert contig_id & blast_id into TABLE contig_blast

100% correct.

>
>
> I can't seem to recover the value of blast_id form a Perl script:
>
> for a test TABLE test:
>
> CREATE TABLE test (
> blast_id SERIAL PRIMARY KEY,
> homolog text
> );
>
>
>         *****************
>
> #!/usr/bin/perl -w
> use strict;
> use Pg;
> my ($conn,$result,$ID);
>
> my $CONNECT = $DBNAME . $DBHOST . $LOGIN . $PWD;
>
> $conn = Pg::connectdb($CONNECT);
> die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status;
>
>
> $conn->exec("INSERT INTO test(homolog) VALUES ('row5');");
>
> $ID = $conn->exec("currval('blastx_id');");

Correction:  $ID = $conn->exec("SELECT currval('blastx_id');");
And are you sure the sequence is named "Blastx_id"?  Normal naming would
be: 'blast_blast_id_seq'

Fnally, if this system ever goes mulit-user, you will want to wrap the
above in a transaction to prevent simultaneous updates from yielding a
deceptive CURRVAL result.

>
> print "ID: $ID\n";
>

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco