information_schema.sql

来自「PostgreSQL7.4.6 for Linux」· SQL 代码 · 共 1,693 行 · 第 1/5 页

/*
 * SQL Information Schema
 * as defined in ISO 9075-2:1999 chapter 20
 *
 * Copyright 2003, PostgreSQL Global Development Group
 *
 * $Id: information_schema.sql,v 1.15.2.5 2003/12/17 22:11:42 tgl Exp $
 */

/*
 * Note: Generally, the definitions in this file should be ordered
 * according to the clause numbers in the SQL standard, which is also the
 * alphabetical order.  In some cases it is convenient or necessary to
 * define one information schema view by using another one; in that case,
 * put the referencing view at the very end and leave a note where it
 * should have been put.
 */


/*
 * 20.2
 * INFORMATION_SCHEMA schema
 */

CREATE SCHEMA information_schema;
GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
SET search_path TO information_schema, public;


-- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.


/*
 * 20.4
 * CARDINAL_NUMBER domain
 */

CREATE DOMAIN cardinal_number AS integer
    CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);


/*
 * 20.5
 * CHARACTER_DATA domain
 */

CREATE DOMAIN character_data AS character varying;


/*
 * 20.6
 * SQL_IDENTIFIER domain
 */

CREATE DOMAIN sql_identifier AS character varying;


/*
 * 20.3
 * INFORMATION_SCHEMA_CATALOG_NAME view
 */

CREATE VIEW information_schema_catalog_name AS
    SELECT CAST(current_database() AS sql_identifier) AS catalog_name;

GRANT SELECT ON information_schema_catalog_name TO PUBLIC;


/*
 * 20.7
 * TIME_STAMP domain
 */

CREATE DOMAIN time_stamp AS timestamp(2)
    DEFAULT current_timestamp(2);


/*
 * 20.9
 * APPLICABLE_ROLES view
 */

CREATE VIEW applicable_roles AS
    SELECT CAST(current_user AS sql_identifier) AS grantee,
           CAST(g.groname AS sql_identifier) AS role_name,
           CAST('NO' AS character_data) AS is_grantable

    FROM pg_group g, pg_user u

    WHERE u.usesysid = ANY (g.grolist)
          AND u.usename = current_user;

GRANT SELECT ON applicable_roles TO PUBLIC;


/*
 * 20.13
 * CHECK_CONSTRAINTS view
 */

CREATE VIEW check_constraints AS
    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
           CAST(rs.nspname AS sql_identifier) AS constraint_schema,
           CAST(con.conname AS sql_identifier) AS constraint_name,
           CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
             AS check_clause
    FROM pg_namespace rs,
         pg_constraint con
           LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
           LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
         pg_user u
    WHERE rs.oid = con.connamespace
          AND u.usesysid = coalesce(c.relowner, t.typowner)
          AND u.usename = current_user
          AND con.contype = 'c';

GRANT SELECT ON check_constraints TO PUBLIC;


/*
 * 20.15
 * COLUMN_DOMAIN_USAGE view
 */

CREATE VIEW column_domain_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
           CAST(nt.nspname AS sql_identifier) AS domain_schema,
           CAST(t.typname AS sql_identifier) AS domain_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name

    FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
         pg_attribute a, pg_user u

    WHERE t.typnamespace = nt.oid
          AND c.relnamespace = nc.oid
          AND a.attrelid = c.oid
          AND a.atttypid = t.oid
          AND t.typowner = u.usesysid
          AND t.typtype = 'd'
          AND c.relkind IN ('r', 'v')
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND u.usename = current_user;

GRANT SELECT ON column_domain_usage TO PUBLIC;


/*
 * 20.16
 * COLUMN_PRIVILEGES
 */

CREATE VIEW column_privileges AS
    SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
           CAST(grantee.name AS sql_identifier) AS grantee,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name,
           CAST(pr.type AS character_data) AS privilege_type,
           CAST(
             CASE WHEN aclcontains(c.relacl,
                                   makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
                  THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable

    FROM pg_attribute a,
         pg_class c,
         pg_namespace nc,
         pg_user u_grantor,
         (
           SELECT usesysid, 0, usename FROM pg_user
           UNION ALL
           SELECT 0, grosysid, groname FROM pg_group
           UNION ALL
           SELECT 0, 0, 'PUBLIC'
         ) AS grantee (usesysid, grosysid, name),
         (SELECT 'SELECT' UNION ALL
          SELECT 'INSERT' UNION ALL
          SELECT 'UPDATE' UNION ALL
          SELECT 'REFERENCES') AS pr (type)

    WHERE a.attrelid = c.oid
          AND c.relnamespace = nc.oid
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND c.relkind IN ('r', 'v')
          AND aclcontains(c.relacl,
                          makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
          AND (u_grantor.usename = current_user
               OR grantee.name = current_user
               OR grantee.name = 'PUBLIC');

GRANT SELECT ON column_privileges TO PUBLIC;


/*
 * 20.17
 * COLUMN_UDT_USAGE view
 */

CREATE VIEW column_udt_usage AS
    SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
           CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
           CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
           CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name

    FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
         (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
           LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
           ON (t.typtype = 'd' AND t.typbasetype = bt.oid)

    WHERE a.attrelid = c.oid
          AND a.atttypid = t.oid
          AND u.usesysid = coalesce(bt.typowner, t.typowner)
          AND nc.oid = c.relnamespace
          AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
          AND u.usename = current_user;

GRANT SELECT ON column_udt_usage TO PUBLIC;


/*
 * 20.18
 * COLUMNS view
 */

CREATE VIEW columns AS
    SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
           CAST(nc.nspname AS sql_identifier) AS table_schema,
           CAST(c.relname AS sql_identifier) AS table_name,
           CAST(a.attname AS sql_identifier) AS column_name,
           CAST(a.attnum AS cardinal_number) AS ordinal_position,
           CAST(
             CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
             AS character_data)
             AS column_default,
           CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
             AS character_data)
             AS is_nullable,

           CAST(
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
                    WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
                    ELSE 'USER-DEFINED' END
             ELSE
               CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
                    WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
                    ELSE 'USER-DEFINED' END
             END
             AS character_data)
             AS data_type,

           CAST(
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (1042, 1043) AND t.typtypmod <> -1
                    THEN t.typtypmod - 4 /* char, varchar */
                    WHEN t.typbasetype IN (1560, 1562) AND t.typtypmod <> -1
                    THEN t.typtypmod /* bit, varbit */
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (1042, 1043) AND a.atttypmod <> -1
                    THEN a.atttypmod - 4
                    WHEN a.atttypid IN (1560, 1562) AND a.atttypmod <> -1
                    THEN a.atttypmod
                    ELSE null END
             END
             AS cardinal_number)
             AS character_maximum_length,

           CAST(
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
             END
             AS cardinal_number)
             AS character_octet_length,

           CAST(
             CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
               WHEN 21 /*int2*/ THEN 16
               WHEN 23 /*int4*/ THEN 32
               WHEN 20 /*int8*/ THEN 64
               WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
               WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
               WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
               ELSE null END
             AS cardinal_number)
             AS numeric_precision,

           CAST(
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
                    WHEN t.typbasetype IN (1700) THEN 10
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
                    WHEN a.atttypid IN (1700) THEN 10
                    ELSE null END
             END
             AS cardinal_number)
             AS numeric_precision_radix,

           CAST(
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
                    WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
                    WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
                    ELSE null END
             END
             AS cardinal_number)
             AS numeric_scale,

           CAST(
             CASE WHEN t.typtype = 'd' THEN
               CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
                    THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
                    WHEN t.typbasetype IN (1186)
                    THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
                    ELSE null END
             ELSE
               CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
                    THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
                    WHEN a.atttypid IN (1186)
                    THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
                    ELSE null END
             END
             AS cardinal_number)
             AS datetime_precision,