Re: inputs for pg_get_id() function? - Mailing list pgsql-novice
From | Richard Broersma Jr |
---|---|
Subject | Re: inputs for pg_get_id() function? |
Date | |
Msg-id | 162471.52475.qm@web31810.mail.mud.yahoo.com Whole thread Raw |
In response to | inputs for pg_get_id() function? (Steve Lefevre <lefevre.10@osu.edu>) |
List | pgsql-novice |
--- Steve Lefevre <lefevre.10@osu.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.
pgsql-novice by date: