currval function problems

[jisoo22]

Hello all,

I'm trying to find out what the internal counter in my database is at to make sure it matches the primary serial key. When I run the command "select('table_field_id_seq');" it says that it is not yet defined in this session. I'm able to use the MAX() function though, which is rather strange. Can anyone tell me what's going on?

Thanks,
Jisoo22

 

[Leland123]

Hi jisoo22,

I'm not sure exactly what your trying to do, but you should be able to view the fields in you sequence. Be sure and connect to the database that owns the sequence. Then you should be able to issue the follow command from the psql prompt:

Select * form table_field_id_seq;

This would select the single sequence row and display all fields.

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[jisoo22]

It's close but not quite what I'm trying to do. I'm trying to call up the most recently entered record from my php form. Unfortunately the table I'm entering data in has a record that was forced in with a primary key number of 200 and cannot be removed for the purposes of this database. For instance, perhaps the last record of data entered was with primary key number 15. I need to pull up that data without having to dirctly reference the number 15, but rather with an condition that says "pull up the last entered record". I think currval() is supposed to do that but it doesn't seem to want to work the first time. Only after I run a couple other functions like MAX() or setval() can I use currval(). Is there any reason for this?

Thanks,
Jisoo22

 

[Leland123]

Hi jisoo22,


I have the following as default for the primary key field in one of my tables, which is to be auto-incremented by the postgreSQL sequence:

nextval('public.client_clientno_seq'::text) ## default value

Whenever I do an insert I make no mention of the primary key to be incremented and postgreSQL automatically preforms the increment from the code in the primary key default.

Once the record has been inserted, I grab the primary key value with the following perl code which should be close to what would be used in php:

########################################################
$sth = $dbh->prepare("SELECT currval('client_clientno_seq')"

$sth->execute or die "Can't execute statement: $DBI::errstr";

$array_ref = $sth->fetchall_arrayref();

foreach my $row (@$array_ref) {

@temp = @$row;

}
########################################################

I can then place the primary key value in a regular perl variable as follows:

$leland = @temp;

Regards

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

 

[jisoo22]

Thanks for the tip, it's much appreciated. Unfortunately I haven't done Perl before, so it's a little difficult to understand. I'm doing this in PHP so I'm not sure if you or anyone else can tell me if this is wrong:

$id_query2 = "SELECT currval('table_field_seq')";
$query = pg_query($connection, $id_query2);
$num = pg_Result($connection, 0, key_id);

Thanks,
Jisoo22

 

[Leland123]

Hi jisoo22,

You can test the code. Immediately after having your php code insert a record, run your code:

########################################
$id_query2 = "SELECT currval('table_field_seq')";
$query = pg_query($connection, $id_query2);
$num = pg_Result($connection, 0, key_id);
########################################

Then use php to print $num to your standard output, and compare it to the value the postgreSQL sequence placed in your primary serial key to see if they are the same. It so, I would guess it is working.

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox