Обсуждение: How to select a list of sequences?
How can I select a list of sequences in Postgres 8.4? I'm writing functions which select names of tables, tablespaces, sequences, etc. For example, I can select a list of table names using the following command: SELECT tablename FROM pg_tables WHERE schemaname = 'public'; However, I can't seem to find a view of sequences. I'm sure it's in there, as you can display a list of sequences using the \ds command, but that doesn't really help me here. Any help? Thanks, Ken
Hi Kenneth,
You can retrieve the sequence information from the pg_catalog "pg_statio_all_sequences"
or
select * from pg_class where relkind='S'
Best Regards,
Raghavendra
EnterpriseDB Corporation
EnterpriseDB Corporation
The Enterprise Postgres Company
On Fri, Mar 4, 2011 at 7:32 PM, Kenneth Buckler <kenneth.buckler@gmail.com> wrote:
How can I select a list of sequences in Postgres 8.4?
I'm writing functions which select names of tables, tablespaces, sequences, etc.
For example, I can select a list of table names using the following command:
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
However, I can't seem to find a view of sequences.
I'm sure it's in there, as you can display a list of sequences using
the \ds command, but that doesn't really help me here.
Any help?
Thanks,
Ken
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank you.
Please do add 'pgsql-general@postgresql.org' while replying.
Best Regards,
Raghavendra
EnterpriseDB Corporation
Raghavendra
EnterpriseDB Corporation
The Enterprise Postgres Company
On Fri, Mar 4, 2011 at 8:04 PM, Kenneth Buckler <kenneth.buckler@gmail.com> wrote:
Perfect! Thanks a bunch!
Ken
On Fri, Mar 4, 2011 at 9:31 AM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:
> Hi Kenneth,
> You can retrieve the sequence information from the pg_catalog
> "pg_statio_all_sequences"
> or
> select * from pg_class where relkind='S'
> Best Regards,
> Raghavendra
> EnterpriseDB Corporation
> The Enterprise Postgres Company
>
>
> On Fri, Mar 4, 2011 at 7:32 PM, Kenneth Buckler <kenneth.buckler@gmail.com>
> wrote:
>>
>> How can I select a list of sequences in Postgres 8.4?
>>
>> I'm writing functions which select names of tables, tablespaces,
>> sequences, etc.
>>
>> For example, I can select a list of table names using the following
>> command:
>>
>> SELECT tablename FROM pg_tables WHERE schemaname = 'public';
>>
>> However, I can't seem to find a view of sequences.
>>
>> I'm sure it's in there, as you can display a list of sequences using
>> the \ds command, but that doesn't really help me here.
>>
>> Any help?
>>
>> Thanks,
>>
>> Ken
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
This might be helpful, as Raghavendrahave a look on relkind
Regards
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Kenneth Buckler <kenneth.buckler@gmail.com>
Cc: pgsql-general@postgresql.org
Sent: Fri, March 4, 2011 3:31:37 PM
Subject: Re: [GENERAL] How to select a list of sequences?
Hi Kenneth,
SELECT
n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
AND n.nspname = 'public'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
AND n.nspname = 'public'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Regards
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Kenneth Buckler <kenneth.buckler@gmail.com>
Cc: pgsql-general@postgresql.org
Sent: Fri, March 4, 2011 3:31:37 PM
Subject: Re: [GENERAL] How to select a list of sequences?
Hi Kenneth,
You can retrieve the sequence information from the pg_catalog "pg_statio_all_sequences"
or
select * from pg_class where relkind='S'
Best Regards,
Raghavendra
EnterpriseDB Corporation
EnterpriseDB Corporation
The Enterprise Postgres Company
On Fri, Mar 4, 2011 at 7:32 PM, Kenneth Buckler <kenneth.buckler@gmail.com> wrote:
How can I select a list of sequences in Postgres 8.4?
I'm writing functions which select names of tables, tablespaces, sequences, etc.
For example, I can select a list of table names using the following command:
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
However, I can't seem to find a view of sequences.
I'm sure it's in there, as you can display a list of sequences using
the \ds command, but that doesn't really help me here.
Any help?
Thanks,
Ken
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general