Thread: FAQ error
Our FAQ, item 4.16.2 has:
$newSerialID = nextval('person_id_seq');INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
Is this correct Perl? I don't see a nextval() function in Perl. Can
you call SQL server-side functions natively from Perl?
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
On 10 Oct 2001 at 17:12 (-0400), Bruce Momjian wrote:
|
| Our FAQ, item 4.16.2 has:
|
| $newSerialID = nextval('person_id_seq');
| INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
|
| Is this correct Perl? I don't see a nextval() function in Perl. Can
| you call SQL server-side functions natively from Perl?
no. The proper perl code would be more like...
use DBI;
my ($lastid,$nextid,$sql,$rv);
my $dbh = DBI->connect("perldoc DBD::Pg");
# to use the nextval
$sql = "SELECT nextval('person_id_seq')";
$nextid = ($dbh->selectrow_array($sql))[0];
$sql = "INSERT INTO person (id, name) VALUES ($nextid, 'Blaise Pascal');
$rv = $dbh->do($sql);
# or to get the currval
$sql = "INSERT INTO person (name) VALUES ('Blaise Pascal');
$rv = $dbh->do($sql);
$sql = "SELECT currval('person_id_seq')";
$lastid = ($dbh->selectrow_array($sql))[0];
| --
| Bruce Momjian | http://candle.pha.pa.us
| pgman@candle.pha.pa.us | (610) 853-3000
| + If your life is a hard drive, | 830 Blythe Avenue
| + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
|
| ---------------------------(end of broadcast)---------------------------
| TIP 6: Have you searched our list archives?
|
| http://archives.postgresql.org
--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman
Bruce Momjian wrote:
> $newSerialID = nextval('person_id_seq');
> INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
>
> Is this correct Perl? I don't see a nextval() function in Perl. Can
> you call SQL server-side functions natively from Perl?
Ofcourse not. This can be counted as 'pseudo-code'...
A correct implementation using DBI (and DBD::Pg) would be
$newSerialID = $dbh->selectrow_array (q{select
nextval('person_id_seq')});
$dbh->do (qq{INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise
Pascal')});
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Bruce Momjian writes:
> Our FAQ, item 4.16.2 has:
>
> $newSerialID = nextval('person_id_seq');
> INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
>
> Is this correct Perl?
No. I always thought it was pseudo code. I think it's fine.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Bruce Momjian writes:
>
> > Our FAQ, item 4.16.2 has:
> >
> > $newSerialID = nextval('person_id_seq');
> > INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> >
> > Is this correct Perl?
>
> No. I always thought it was pseudo code. I think it's fine.
It is psaudo-code, but the assignment for nextval() is just wrong:
> > $newSerialID = nextval('person_id_seq');
I am going to flesh this out with the SELECT but not the rest.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
> On 10 Oct 2001 at 17:12 (-0400), Bruce Momjian wrote:
> |
> | Our FAQ, item 4.16.2 has:
> |
> | $newSerialID = nextval('person_id_seq');
> | INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> |
> | Is this correct Perl? I don't see a nextval() function in Perl. Can
> | you call SQL server-side functions natively from Perl?
>
> no. The proper perl code would be more like...
>
> use DBI;
> my ($lastid,$nextid,$sql,$rv);
> my $dbh = DBI->connect("perldoc DBD::Pg");
>
> # to use the nextval
> $sql = "SELECT nextval('person_id_seq')";
> $nextid = ($dbh->selectrow_array($sql))[0];
> $sql = "INSERT INTO person (id, name) VALUES ($nextid, 'Blaise Pascal');
> $rv = $dbh->do($sql);
OK, new FAQ code is:
$sql = "SELECT nextval('person_id_seq')"; $newSerialID = ($conn->selectrow_array($sql))[0]; INSERT INTO person
(id,name) VALUES ($newSerialID, 'Blaise Pascal'); $res = $dbh->do($sql);
>
> # or to get the currval
> $sql = "INSERT INTO person (name) VALUES ('Blaise Pascal');
> $rv = $dbh->do($sql);
> $sql = "SELECT currval('person_id_seq')";
> $lastid = ($dbh->selectrow_array($sql))[0];
and:
INSERT INTO person (name) VALUES ('Blaise Pascal'); $res = $conn->do($sql); $sql = "SELECT
currval('person_id_seq')"; $newSerialID = ($conn->selectrow_array($sql))[0];
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian writes:
> OK, new FAQ code is:
>
> $sql = "SELECT nextval('person_id_seq')";
> $newSerialID = ($conn->selectrow_array($sql))[0];
> INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> $res = $dbh->do($sql);
This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Bruce Momjian writes:
>
> > OK, new FAQ code is:
> >
> > $sql = "SELECT nextval('person_id_seq')";
> > $newSerialID = ($conn->selectrow_array($sql))[0];
> > INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> > $res = $dbh->do($sql);
>
> This code is still incorrect for any known programming language and it's
> even less clear to a person that doesn't know the programming language
> it's probably trying to imitate.
OK, what suggestions do you have?
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Bruce Momjian writes:
> > Bruce Momjian writes:
> >
> > > OK, new FAQ code is:
> > >
> > > $sql = "SELECT nextval('person_id_seq')";
> > > $newSerialID = ($conn->selectrow_array($sql))[0];
> > > INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> > > $res = $dbh->do($sql);
> >
> > This code is still incorrect for any known programming language and it's
> > even less clear to a person that doesn't know the programming language
> > it's probably trying to imitate.
>
> OK, what suggestions do you have?
I didn't have a problem with the original version. It conveyed clearly
(to me), "read the nextval and insert it yourself".
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Bruce Momjian writes:
>
> > > Bruce Momjian writes:
> > >
> > > > OK, new FAQ code is:
> > > >
> > > > $sql = "SELECT nextval('person_id_seq')";
> > > > $newSerialID = ($conn->selectrow_array($sql))[0];
> > > > INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
> > > > $res = $dbh->do($sql);
> > >
> > > This code is still incorrect for any known programming language and it's
> > > even less clear to a person that doesn't know the programming language
> > > it's probably trying to imitate.
> >
> > OK, what suggestions do you have?
>
> I didn't have a problem with the original version. It conveyed clearly
> (to me), "read the nextval and insert it yourself".
Obviously, someone did because they tried the code and it didn't work.
At least the new code is closer to valid, though less clear. It is at
least a valid snippet, which the previous version was not.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
> Obviously, someone did because they tried the code and it didn't work.
> At least the new code is closer to valid, though less clear. It is at
> least a valid snippet, which the previous version was not.
OK, I changed it to more pseudocode:
new_id = output of "SELECT nextval('person_id_seq')" INSERT INTO person (id, name) VALUES (new_id, 'Blaise
Pascal');
and
INSERT INTO person (name) VALUES ('Blaise Pascal'); new_id = output of "SELECT currval('person_id_seq')";
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026