Re: SQL equivallent to "\ds" in psql

On 17 Oct 2001, Arcady Genkin wrote:

> Where does Postgres store information about the sequences? I tried
> looking in the tables produced by "\dS", but could find no references
> to the sequences. :(

Oops, I thought you had made a typo, but I made a thinko. Use the -E
option to generate the SQL to pull out sequences:

arc=> create sequence testme;
CREATE
arc=> \ds
********* QUERY *********
SELECT c.relname as "Name",
(CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
(CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid = c.relowner) AND
relkind in ('S')
AND c.relname !~ '^pg_'

ORDER BY "Name"
*************************

List of relations
Name | Type | Owner
--------+----------+-----------
testme | sequence | arc_admin
(1 row)

-- Brett
http://www.chapelperilous.net/
------------------------------------------------------------------------
Sometime when you least expect it, Love will tap you on the shoulder...
and ask you to move out of the way because it still isn't your turn.
-- N.V. Plyter