<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.89 2004/07/04 23:34:23 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.90 2004/10/11 17:24:39 tgl Exp $

-->

<chapter id="catalogs">

<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>

<entry>name of index</entry>

</row>

<row>

<entry><structfield>tablespace</structfield></entry>

<entry><type>name</type></entry>

<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.spcname</literal></entry>

<entry>name of tablespace containing index (NULL if default for database)</entry>

</row>

<row>

<entry><structfield>indexdef</structfield></entry>

<entry><type>text</type></entry>

<entry><literal><link linkend="catalog-pg-shadow"><structname>pg_shadow</structname></link>.usename</literal></entry>

<entry>name of table's owner</entry>

</row>

<row>

<entry><structfield>tablespace</structfield></entry>

<entry><type>name</type></entry>

<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.spcname</literal></entry>

<entry>name of tablespace containing table (NULL if default for database)</entry>

</row>

<row>

<entry><structfield>hasindexes</structfield></entry>

<entry><type>boolean</type></entry>

N.nspnameAS schemaname,

C.relnameAS tablename,

T.spcnameAS tablespace,

pg_get_userbyid(C.relowner)AS tableowner,

T.spcnameAS tablespace,

C.relhasindexAS hasindexes,

C.relhasrulesAS hasrules,

(C.reltriggers>0)AS hastriggers

N.nspnameAS schemaname,

C.relnameAS tablename,

T.spcnameAS tablespace,

I.relnameAS indexname,

T.spcnameAS tablespace,

pg_get_indexdef(I.oid)AS indexdef

FROM pg_index XJOIN pg_class CON (C.oid=X.indrelid)

JOIN pg_class ION (I.oid=X.indexrelid)

LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)

LEFT JOIN pg_tablespace TON (T.oid=C.reltablespace)

LEFT JOIN pg_tablespace TON (T.oid=I.reltablespace)

WHEREC.relkind='r'ANDI.relkind='i';

FROM pg_lock_status()AS L(relationoid, databaseoid,

transaction xid, pid int4, modetext, grantedboolean);

transaction xid, pid int4, modetext, grantedboolean);

viewname | definition

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

iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);

pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename,t.spcname AS"tablespace", i.relname ASindexname, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid =c.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));

pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename,i.relname ASindexname, t.spcname AS"tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid =i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));

pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status() l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean);

pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);

pg_settings | SELECT a.name, a.setting, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text);

pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE (((pg_statio_all_sequences.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_sequences.schemaname <> 'pg_toast'::name)) AND (pg_statio_all_sequences.schemaname <> 'information_schema'::name));

pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE (((pg_statio_all_tables.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_tables.schemaname <> 'pg_toast'::name)) AND (pg_statio_all_tables.schemaname <> 'information_schema'::name));

pg_stats | SELECT nspname AS schemaname, relname AS tablename, attname, stanullfrac AS null_frac, stawidth AS avg_width, stadistinct AS n_distinct, CASE 1 WHEN stakind1 THEN stavalues1 WHEN stakind2 THEN stavalues2 WHEN stakind3 THEN stavalues3 WHEN stakind4 THEN stavalues4 ELSE NULL::"unknown" END AS most_common_vals, CASE 1 WHEN stakind1 THEN stanumbers1 WHEN stakind2 THEN stanumbers2 WHEN stakind3 THEN stanumbers3 WHEN stakind4 THEN stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE 2 WHEN stakind1 THEN stavalues1 WHEN stakind2 THEN stavalues2 WHEN stakind3 THEN stavalues3 WHEN stakind4 THEN stavalues4 ELSE NULL::"unknown" END AS histogram_bounds, CASE 3 WHEN stakind1 THEN stanumbers1[1] WHEN stakind2 THEN stanumbers2[1] WHEN stakind3 THEN stanumbers3[1] WHEN stakind4 THEN stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE has_table_privilege(c.oid, 'select'::text);

pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename,t.spcname AS "tablespace",pg_get_userbyid(c.relowner) AS tableowner, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");

pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");

pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;

pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");

rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;