Skip site navigation (1) Skip section navigation (2)

Re: inputs for pg_get_id() function?

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Steve Lefevre <lefevre(dot)10(at)osu(dot)edu>, pgsql-novice(at)postgresql(dot)org
Subject: Re: inputs for pg_get_id() function?
Date: 2007-06-13 03:56:39
Message-ID: 162471.52475.qm@web31810.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- Steve Lefevre <lefevre(dot)10(at)osu(dot)edu> wrote:
> Now I'm working on a PHP function that I'm calling pg_get_id(). When I 
> first wrote it, my inputs were $table_name and $column_name, but I 
> quickly found out that I have to reference the name of the columns'  
> sequence, not the column itself, to get the id. Now it's working.
> 
> But, I'm wondering it I can even get rid of the $sequence_name input. If 
> I assume that a user will only call the function to get an number for a 
> primary key column that is a sequence, is there a query I can do to find 
> out the primary key of a table, and the name of it's sequence? In 
> psuedo-code:
> 
> function pg_get_id ( $table ) {
> 
>    "SELECT primary_key_column_name FROM $table"
> 
>    "SELECT column_sequence_name FROM $table "
> 
>    "SELECT nextval('column_sequence_name') FROM $table"
> 
>    return $nextval;
> }

While this is possible it really isn't necessary.  You really don't need to know the name of the
sequence, once it is implemented as a primary key default value.  Below are three ways to specify
the next value for a primary key.

for instance:

mydb=# CREATE TABLE Apple (
mydb(# apple_nbr Serial PRIMARY KEY,
mydb(# variety Varchar UNIQUE NOT NULL
mydb(# );
NOTICE:  CREATE TABLE will create implicit sequence "apple_apple_nbr_seq" for serial column
"apple.apple_nbr"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "apple_pkey" for table "apple"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "apple_variety_key" for table "apple"
CREATE TABLE
mydb=#
mydb=# --INSERTING specifying all the columns
mydb=#
mydb=# INSERT INTO APPLE( apple_nbr, variety ) VALUES( DEFAULT, 'Red Delicous');
INSERT 0 1
mydb=#
mydb=# --INSERTING by only specifying columns of interest
mydb=#
mydb=# INSERT INTO APPLE( variety) VALUES( 'Fugi' );  -- this uses the implied DEFAULT
INSERT 0 1
mydb=#
mydb=# SELECT * FROM Apple;
 apple_nbr |   variety
-----------+--------------
         1 | Red Delicous
         2 | Fugi
(2 rows)

mydb=#
mydb=# \d apple
                                   Table "public.apple"
  Column   |       Type        |                         Modifiers
-----------+-------------------+-----------------------------------------------------------
 apple_nbr | integer           | not null default nextval('apple_apple_nbr_seq'::regclass)
 variety   | character varying | not null
Indexes:
    "apple_pkey" PRIMARY KEY, btree (apple_nbr)
    "apple_variety_key" UNIQUE, btree (variety)

mydb=#
mydb=# --IF you want to specify the next value yourself and not use a default
mydb=#
mydb=# INSERT INTO APPLE( apple_nbr, variety ) VALUES ( nextval('apple_apple_nbr_seq'), 'Pink
Lady');
INSERT 0 1
mydb=#
mydb=# select * from apple;
 apple_nbr |   variety
-----------+--------------
         1 | Red Delicous
         2 | Fugi
         3 | Pink Lady
(3 rows)

mydb=#

Regards,
Richard Broersma Jr.

In response to

Responses

pgsql-novice by date

Next:From: NabilDate: 2007-06-13 15:05:25
Subject: Mapping one to many
Previous:From: Steve LefevreDate: 2007-06-13 02:43:42
Subject: inputs for pg_get_id() function?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group