Re: [GENERAL] determine sequence name for a serial
On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote: # CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS ' # SELECT adsrc # FROM pg_attrdef, pg_class, pg_namespace, pg_attribute # WHERE # adrelid = pg_class.oid AND # pg_class.relnamespace = pg_namespace.oid AND # pg_attribute.attnum = pg_attrdef.adnum AND # pg_attribute.attrelid = pg_class.oid AND # pg_namespace.nspname = $1 AND # pg_class.relname = $2 AND # pg_attribute.attname = $3; # ' language sql; As per Tom's mention of pg_depend, here's something that seems to do the trick for the time being, assuming the column is a serial: -- get_sequence(schema_name, table_name, column_name) CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS text AS ' SELECT seq.relname::text FROM pg_class src, pg_class seq, pg_namespace, pg_attribute, pg_depend WHERE pg_depend.refobjsubid = pg_attribute.attnum AND pg_depend.refobjid = src.oid AND seq.oid = pg_depend.objid AND src.relnamespace = pg_namespace.oid AND pg_attribute.attrelid = src.oid AND pg_namespace.nspname = $1 AND src.relname = $2 AND pg_attribute.attname = $3; ' language sql; Thanks, this seems to work well. My goal is to actually create a php function that takes a result and returns the insert_id like mysql_insert_id() does, but without needing to know the sequence names and such. I would make a psql function, but I don't always have that option with some clients existing systems. -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development * --- Now supporting PHP5 and PHP4 --- / signature.asc Description: This is a digitally signed message part
Re: [GENERAL] determine sequence name for a serial
On Thursday October 28 2004 11:42, Robby Russell wrote: Thanks, this seems to work well. My goal is to actually create a php function that takes a result and returns the insert_id like mysql_insert_id() does, but without needing to know the sequence names and such. I would make a psql function, but I don't always have that option with some clients existing systems. An alternative is to simply select nextval() from a separately-created sequence object to get the serial value, then insert with that value. No need to have a serial column then, but you do need to explicitly create the sequence object, as opposed to SERIAL. But I didn't understand why you care to get rid of the explicit reference to the sequence object in your code in the first place. In PostgreSQL, at least for the past 5 years if not longer, if you create a SERIAL column for (schemaname, tablename, columnname), then your sequence will *always* be schemaname.tablename_columnname_seq. If that naming convention changes, there will be a whole lotta breakage world-wide. Ed ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] determine sequence name for a serial
On Thu, 2004-10-28 at 16:51 -0600, Ed L. wrote: On Thursday October 28 2004 11:42, Robby Russell wrote: Thanks, this seems to work well. My goal is to actually create a php function that takes a result and returns the insert_id like mysql_insert_id() does, but without needing to know the sequence names and such. I would make a psql function, but I don't always have that option with some clients existing systems. An alternative is to simply select nextval() from a separately-created sequence object to get the serial value, then insert with that value. No need to have a serial column then, but you do need to explicitly create the sequence object, as opposed to SERIAL. nextval, currval, either way, I would need to know the specific sequence name. Was looking for a good way to pass a function a schema and table and return a sequence. I got exactly what I was looking for and have been able to build a function that will handle this for me. It's part of a db layer class that I use with mysql and pgsql, and was using mysql_insert_id and wanted to model a function that would return an id like the mysql_insert_id function does. (one of the few pgsql/php functions that doesn't exist in php natively..but does with mysql) -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development *--- Now supporting PHP5 --- / signature.asc Description: This is a digitally signed message part
Re: [GENERAL] determine sequence name for a serial
On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote: But I didn't understand why you care to get rid of the explicit reference to the sequence object in your code in the first place. In PostgreSQL, at least for the past 5 years if not longer, if you create a SERIAL column for (schemaname, tablename, columnname), then your sequence will *always* be schemaname.tablename_columnname_seq. Only for certain values of always. Tables and columns can be renamed, so the sequence name might no longer be tablename_columnname_seq, but rather oldtablename_oldcolumnname_seq. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] determine sequence name for a serial
# But I didn't understand why you care to get rid of the explicit reference to # the sequence object in your code in the first place. In PostgreSQL, at # least for the past 5 years if not longer, if you create a SERIAL column for # (schemaname, tablename, columnname), then your sequence will *always* be # schemaname.tablename_columnname_seq. If that naming convention changes, # there will be a whole lotta breakage world-wide. When a table is renamed, related sequences' names don't change (as of 7.4.5). The ability to automagically pull the sequence based on the schema.table.column would be nice if you don't want to worry about having to update your table name and sequence name references in code. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] determine sequence name for a serial
On Thursday October 28 2004 5:31, Michael Fuhr wrote: On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote: But I didn't understand why you care to get rid of the explicit reference to the sequence object in your code in the first place. In PostgreSQL, at least for the past 5 years if not longer, if you create a SERIAL column for (schemaname, tablename, columnname), then your sequence will *always* be schemaname.tablename_columnname_seq. Only for certain values of always. Tables and columns can be renamed, so the sequence name might no longer be tablename_columnname_seq, but rather oldtablename_oldcolumnname_seq. Your point is well taken, I see the gotcha there, and thus the value of a function. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] determine sequence name for a serial
Ed L. [EMAIL PROTECTED] writes: In PostgreSQL, at least for the past 5 years if not longer, if you create a SERIAL column for (schemaname, tablename, columnname), then your sequence will *always* be schemaname.tablename_columnname_seq. If that naming convention changes, there will be a whole lotta breakage world-wide. I hope you're wrong about people expecting that to be true because it isn't. The resulting sequence name is limited to 63 characters and gets truncated if it goes over. (63!? was it intended to be 64?) I believe the limit used to be 32 characters too. In any case it's just plain good design to avoid unnecessary interrelationships between different parts of the code. Practically speaking it makes renaming something not involve an error-prone search and replace. More importantly it makes it easier to verify that a piece of code is correct without having to hunt down all the related bits to be sure the relationships are correct. It also makes it possible to reuse or refactor the code. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] determine sequence name for a serial
On Thu, Oct 28, 2004 at 07:55:51PM -0400, Greg Stark wrote: Ed L. [EMAIL PROTECTED] writes: In PostgreSQL, at least for the past 5 years if not longer, if you create a SERIAL column for (schemaname, tablename, columnname), then your sequence will *always* be schemaname.tablename_columnname_seq. If that naming convention changes, there will be a whole lotta breakage world-wide. I hope you're wrong about people expecting that to be true because it isn't. The resulting sequence name is limited to 63 characters and gets truncated if it goes over. (63!? was it intended to be 64?) I believe the limit used to be 32 characters too. The NAMEDATALEN constant is defined to be 64, and that includes the trailing \0, so identifiers are limited to 63 bytes. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Before you were born your parents weren't as boring as they are now. They got that way paying your bills, cleaning up your room and listening to you tell them how idealistic you are. -- Charles J. Sykes' advice to teenagers ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] determine sequence name for a serial
I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.
For example,
CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
\d foo
Table public.foo
Column | Type | Modifiers
+-+-
id | integer | not null default nextval('public.foo_id_seq'::text)
bar| text|
Indexes:
foo_pkey primary key, btree (id)
Now, I have figured out how to get a list of all the sequences with:
foo= SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
relname
foo_id_seq
(1 row)
I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and
pg_class in order to find the sequence for a specific field in public.foo.
Can anyone point me in the right direction? I am trying to get out of the habit of
hard-coding the sequence names in my code.
Now that I think of it, I am lacking 'public.' as well from my query.
Ok, so how would I go about getting the sequence name for a SERIAL field on any given
schema.table? I would like to build a function that would return this value if I pass
it the schema and table (and fieldname is necessary)
Thanks,
Robby
--
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | [EMAIL PROTECTED]
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting Development
* --- Now supporting PHP5 and PHP4 ---
/
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] determine sequence name for a serial
On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:
I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.
For example,
CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
\d foo
Table public.foo
Column | Type | Modifiers
+-+-
id | integer | not null default nextval('public.foo_id_seq'::text)
bar| text|
Indexes:
foo_pkey primary key, btree (id)
Now, I have figured out how to get a list of all the sequences with:
foo= SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
relname
foo_id_seq
(1 row)
I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and
pg_class in order to find the sequence for a specific field in public.foo.
Can anyone point me in the right direction? I am trying to get out of the habit of
hard-coding the sequence names in my code.
Now that I think of it, I am lacking 'public.' as well from my query.
Ok, so how would I go about getting the sequence name for a SERIAL field on any
given schema.table? I would like to build a function that would return this value if
I pass it the schema and table (and fieldname is necessary)
Thanks,
I figured out how to get this:
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
adsrc
nextval('public.foo_id_seq'::text)
(1 row)
However, this will break as soon as I do this:
foo= CREATE SCHEMA x;
CREATE SCHEMA
foo= CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
NOTICE: CREATE TABLE will create implicit sequence foo_id_seq for
serial column foo.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
foo_pkey for table foo
CREATE TABLE
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
ERROR: more than one row returned by a subquery used as an expression
So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.
Help. :-)
Thanks again,
-Robby
--
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | [EMAIL PROTECTED]
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting Development
* --- Now supporting PHP5 and PHP4 ---
/
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] determine sequence name for a serial
On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:
I figured out how to get this:
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
adsrc
nextval('public.foo_id_seq'::text)
(1 row)
However, this will break as soon as I do this:
foo= CREATE SCHEMA x;
CREATE SCHEMA
foo= CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
NOTICE: CREATE TABLE will create implicit sequence foo_id_seq for
serial column foo.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
foo_pkey for table foo
CREATE TABLE
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
ERROR: more than one row returned by a subquery used as an expression
So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.
Also, I am trying to avoid assuming that the sequence name will be:
schema.table_id_seq
The goal is to determine the sequence name for any schema.table that has
a SERIAL sequence (because you can create a sequence with a different
name) ... and if the column name isn't 'id'
for example:
foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'bar');
adsrc
-
nextval('public.bar_nid_seq'::text)
(1 row)
The schema.table_id_seq wouldn't work under this scenario.
any thoughts or pointers?
Thanks,
Robby
--
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | [EMAIL PROTECTED]
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting Development
* --- Now supporting PHP5 and PHP4 ---
/
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] determine sequence name for a serial
On Wed, Oct 27, 2004 at 09:33:21PM -0700, Robby Russell wrote: So, it was a nice attempt, but I am back to the need to of determining the sequence name using a schema and a table. The schema of a table is stored in pg_class.relnamespace, which is an Oid of the pg_namespace catalog. With that and your previous query you should be set. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La felicidad no es mañana. La felicidad es ahora ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] determine sequence name for a serial
Robby Russell [EMAIL PROTECTED] writes: Ok, so how would I go about getting the sequence name for a SERIAL field on any given schema.table? 8.0 will have a function pg_get_serial_sequence to do this for you. If you can't wait, the secret is to look in pg_depend for the dependency link from the serial sequence to its column. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] determine sequence name for a serial
# I figured out how to get this:
#
# foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
#adsrc
#
# nextval('public.foo_id_seq'::text)
# (1 row)
#
# However, this will break as soon as I do this:
#
# foo= CREATE SCHEMA x;
# CREATE SCHEMA
# foo= CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
# NOTICE: CREATE TABLE will create implicit sequence foo_id_seq for
# serial column foo.id
# NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
# foo_pkey for table foo
# CREATE TABLE
# foo= SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
# ERROR: more than one row returned by a subquery used as an
# expression
This should suffice to get you a string you can regex. Other than the
default value setting for the serial, I don't see another link that
binds the serial to its sequence.
CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS '
SELECT adsrc
FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
WHERE
adrelid = pg_class.oid AND
pg_class.relnamespace = pg_namespace.oid AND
pg_attribute.attnum = pg_attrdef.adnum AND
pg_attribute.attrelid = pg_class.oid AND
pg_namespace.nspname = $1 AND
pg_class.relname = $2 AND
pg_attribute.attname = $3;
' language sql;
--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication Support Services, (503) 667-4564
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] determine sequence name for a serial
On Wed, Oct 27, 2004 at 09:06:15PM -0700, Robby Russell wrote:
Ok, so how would I go about getting the sequence name for a SERIAL
field on any given schema.table? I would like to build a function
that would return this value if I pass it the schema and table (and
fieldname is necessary)
PostgreSQL 8.0 (still in beta) has pg_get_serial_sequence():
test= SELECT pg_get_serial_sequence('foo', 'id');
pg_get_serial_sequence
public.foo_id_seq
Here's a query that you might find useful:
SELECT s1.nspname || '.' || t1.relname AS tablename,
a.attname,
s2.nspname || '.' || t2.relname AS sequencename
FROM pg_depend AS d
JOIN pg_class AS t1 ON t1.oid = d.refobjid
JOIN pg_class AS t2 ON t2.oid = d.objid
JOIN pg_namespace AS s1 ON s1.oid = t1.relnamespace
JOIN pg_namespace AS s2 ON s2.oid = t2.relnamespace
JOIN pg_attribute AS a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE t1.relkind = 'r'
AND t2.relkind = 'S';
I posted a somewhat different query in a recent thread about
automatically updating all sequences after importing data:
http://archives.postgresql.org/pgsql-general/2004-10/msg00673.php
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] determine sequence name for a serial
# CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS ' # SELECT adsrc # FROM pg_attrdef, pg_class, pg_namespace, pg_attribute # WHERE # adrelid = pg_class.oid AND # pg_class.relnamespace = pg_namespace.oid AND # pg_attribute.attnum = pg_attrdef.adnum AND # pg_attribute.attrelid = pg_class.oid AND # pg_namespace.nspname = $1 AND # pg_class.relname = $2 AND # pg_attribute.attname = $3; # ' language sql; As per Tom's mention of pg_depend, here's something that seems to do the trick for the time being, assuming the column is a serial: -- get_sequence(schema_name, table_name, column_name) CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS text AS ' SELECT seq.relname::text FROM pg_class src, pg_class seq, pg_namespace, pg_attribute, pg_depend WHERE pg_depend.refobjsubid = pg_attribute.attnum AND pg_depend.refobjid = src.oid AND seq.oid = pg_depend.objid AND src.relnamespace = pg_namespace.oid AND pg_attribute.attrelid = src.oid AND pg_namespace.nspname = $1 AND src.relname = $2 AND pg_attribute.attname = $3; ' language sql; -- Jonathan Daugherty http://www.cprogrammer.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
