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