
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, Pennsylvania 19026

Wed, 10 Oct 2001 17:12:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
FAQ error
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

Wed, 10 Oct 2001 20:20:46 -0400
Brent Verner <brent@rcfile.org>
Re: FAQ error
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

Thu, 11 Oct 2001 14:31:36 +0300
Alessio Bragadini <alessio@albourne.com>
Re: FAQ error
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


Thu, 11 Oct 2001 20:34:07 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FAQ error
> 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, Pennsylvania 19026

Thu, 11 Oct 2001 15:46:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FAQ error
> 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, Pennsylvania 19026

Fri, 12 Oct 2001 23:52:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FAQ error
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


Sat, 13 Oct 2001 21:23:43 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FAQ error
> 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, Pennsylvania 19026

Sat, 13 Oct 2001 15:47:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FAQ error
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


Sun, 14 Oct 2001 13:43:53 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FAQ error
> 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, Pennsylvania 19026

Sun, 14 Oct 2001 19:03:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FAQ error
> 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, Pennsylvania 19026

Sun, 14 Oct 2001 19:27:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FAQ error