确定序列的序列名称 [英] determine sequence name for a serial

问题描述

我试图找到一种确定序列名称的方法

对于SERIAL是在postgresql中。


例如，


CREATE TABLE foo（id SERIAL PRIMARY KEY NOT NULL，bar TEXT）;


\d foo

Table " public.foo"

列|输入|修饰符

-------- + --------- + ---------------------- -------------------------------

id |整数| not null default nextval（''public.foo_id_seq'':: text）

bar |文字|

索引：

" foo_pkey"主键，btree（id）


现在，我已经想出如何获得所有序列的列表：


foo = > SELECT relname FROM pg_class WHERE relkind =''S''AND relname！〜''^ pg_'';

relname

---------- -

foo_id_seq

（1行）


我可以在pg_tables中找到public.foo，但我不知道如何关联pg_tables和pg_class以便在public.foo中找到特定字段的序列。


有人能指出我正确的方向吗？我试图摆脱在我的代码中硬编码序列名称的习惯。


现在我想到了，我缺乏'公开'。'以及我的查询。


好​​的，那么如何在给定的schema.table上获取SERIAL字段的序列名称呢？如果我传递架构和表格（并且需要字段名称），我想构建一个能够恢复其价值的函数。


谢谢，


罗比

-

/ ***************************** **********

* Robby Russell | Owner.Developer.Geek

* PLANET ARGON | www.planetargon.com

* Portland，OR | ro***@planetargon.com

* 503.351.4730 | blog.planetargon.com

* PHP / PostgreSQL Hosting&开发

* ---现在支持PHP5和PHP4 ---

********************* ******************* /


-----开始PGP签名-----

版本：GnuPG v1.2.6（GNU / Linux）

iD8DBQBBgHA30QaQZBaqXgwRAkcMAJ4vTrjsPBA + UWsbOR / sy4KHofIOEgCeNS3X

WQk4 + eh0LUgeeIosTGKISVM =

= 3fZd

----- END PGP SIGNATURE -----

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 onany given schema.table? I would like to build a function that would returnthis 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 | ro***@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHA30QaQZBaqXgwRAkcMAJ4vTrjsPBA+UWsbOR/sy4KHofIOEgCeNS3X
WQk4+eh0LUgeeIosTGKISVM=
=3fZd
-----END PGP SIGNATURE-----

推荐答案

On周三，2004-10-27 21：06 -0700，Robby Russell写道：

On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:

我试图找出一种方法来确定SERIAL的序列名是什么postgresql。

例如，

CREATE TABLE foo（id SERIAL PRIMARY KEY NOT NULL，bar TEXT）;

\d foo public.foo
列|输入|修饰符
-------- + --------- + -------------------------- ---------------------------
id |整数| not null default nextval（''public.foo_id_seq'':: text）
bar |文字|
索引：
" foo_pkey"主键，btree（id）

现在，我已经想出了如何获得所有序列的列表：

foo => SELECT relname FROM pg_class WHERE relkind =''S''AND relname！~'' ^ pg_'';
relname
------------
foo_id_seq
（1行）

我可以在pg_tables中找到public.foo，但我不知道如何关联pg_tables和pg_class以便在public.foo中找到特定字段的序列。

有人能指出我正确的方向吗？我试图摆脱代码中硬编码序列名称的困扰。

现在我想到了，我缺乏'公开'。'以及我的查询。

好的，那么如何在任何给定的schema.table上获取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 thehabit 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,

我想如何得到这个：


foo => SELECT adsrc FROM pg_attrdef WHERE adrelid =（SELECT oid FROM

pg_class WHERE relname =''foo''）;

adsrc

--- ---------------------------------

nextval（''public.foo_id_seq''' ::文字）

（1排）


但是，一旦我这样做就会中断：


foo => CREATE SCHEMA x;

CREATE SCHEMA

foo => CREATE TABLE x.foo（id SERIAL PRIMARY KEY NOT NULL，x TEXT）;

注意：CREATE TABLE将创建隐式序列foo_id_seq for

" serial"列foo.id

注意：CREATE TABLE / PRIMARY KEY将创建隐式索引

" foo_pkey"表格foo

创建表格

foo => SELECT adsrc FROM pg_attrdef WHERE adrelid =（SELECT oid FROM

pg_class WHERE relname =''foo''）;

错误：用作子查询返回的多行一个表达


所以，这是一个不错的尝试，但我回到需要使用模式和表来确定序列名称。


帮助。 :-)


再次感谢，


-Robby


-

/ *******************************************

* Robby Russell | Owner.Developer.Geek

* PLANET ARGON | www.planetargon.com

* Portland，OR | ro***@planetargon.com

* 503.351.4730 | blog.planetargon.com

* PHP / PostgreSQL Hosting&开发

* ---现在支持PHP5和PHP4 ---

********************* ******************* /


-----开始PGP签名-----

版本：GnuPG v1.2.6（GNU / Linux）

iD8DBQBBgHaR0QaQZBaqXgwRAn1hAJ9ZnPngtDrOw1kP / fbklTXhY / j5QACeMA2w

pZlgGypzY9L53C3LU4lrwm0 =

= cpsO

----- END PGP SIGNATURE -----

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 | ro***@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHaR0QaQZBaqXgwRAn1hAJ9ZnPngtDrOw1kP/fbklTXhY/j5QACeMA2w
pZlgGypzY9L53C3LU4lrwm0=
=cpsO
-----END PGP SIGNATURE-----

2004年3月27日星期三21:33 -0700，Robby Russell写道：

On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:

我想出了如何得到这个：

foo => SELECT adsrc FROM pg_attrdef WHERE adrelid =（SELECT oid FROM
pg_class WHERE relname =''foo''）;
adsrc
--------------- ---------------------
nextval（''public.foo_id_seq'':: text）
（1行）

但是，一旦我这样做就会中断：

foo => CREATE SCHEMA x;
CREATE SCHEMA
foo => CREATE TABLE x.foo（id SERIAL PRIMARY KEY NOT NULL，x TEXT）;
注意：CREATE TABLE将创建隐式序列foo_id_seq。 for
serial列foo.id
注意：CREATE TABLE / PRIMARY KEY将创建隐式索引
foo_pkey表格foo
创建表格
foo => SELECT adsrc FROM pg_attrdef WHERE adrelid =（SELECT oid FROM
pg_class WHERE relname =''foo''）;
错误：用作表达式的子查询返回的多行

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.

另外，我试图避免假设序列名称为：

schema.table_id_seq


目标是确定序列名称对于任何具有

a SERIAL序列的schema.table（因为您可以创建一个具有不同

名称的序列）...并且如果列名称不是'' 'id''


例如：

foo => SELECT adsrc FROM pg_attrdef WHERE adrelid =（SELECT oid FROM

pg_class WHERE relname =''bar''）;

adsrc

--- ----------------------------------

nextval（''public.bar_nid_seq' ':: text）

（1行）


schema.table_id_seq在这种情况下不起作用。


任何想法或指示？


谢谢，


Robby


-

/ *************************************** />
* Robby Russell | Owner.Developer.Geek

* PLANET ARGON | www.planetargon.com

* Portland，OR | ro***@planetargon.com

* 503.351.4730 | blog.planetargon.com

* PHP / PostgreSQL Hosting&开发

* ---现在支持PHP5和PHP4 ---

********************* ******************* /


-----开始PGP签名-----

版本：GnuPG v1.2.6（GNU / Linux）

iD8DBQBBgHvC0QaQZBaqXgwRAks / AJ9HBpA7l / N0z3x / nBOcyPFX0 / BdJgCghPqL

lC5 + 82mJUmyUclppDcyedD8 =

= EikY

-----结束PGP签名-----

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 | ro***@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHvC0QaQZBaqXgwRAks/AJ9HBpA7l/N0z3x/nBOcyPFX0/BdJgCghPqL
lC5+82mJUmyUclppDcyedD8=
=EikY
-----END PGP SIGNATURE-----

周三， 2004年10月27日下午09：33：21 -0700，Robby Russell写道：

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.

表格的模式存储在pg_class.relnamespace中，这是一个

pg_namespace目录的Oid。有了这个和你之前的查询，你应该设置
。


-

Alvaro Herrera（< alvherre [a] dcc.uchile.cl>）

" La felicidad no es ma？ana。 La felicidad es ahora

---------------------------（播出结束）----- ----------------------

提示4：不要杀死-9''邮政局长

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

这篇关于确定序列的序列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！