--
-- $Header$
--

GRANT USAGE ON SCHEMA pg_tools TO PUBLIC;

CREATE TABLE psql_commands
( match_order serial primary key
, nargs integer not null
, cmd_expression varchar(50) not null
, table_title varchar(60) not null
, table_query text not null
, info_query text
) WITHOUT OIDS;

GRANT SELECT ON psql_commands TO PUBLIC;


--
-- \d[tivsS]+
--
-- List of relations
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^d[tivsS]*$', 'List of relations',
'SELECT n.nspname AS "Schema",
        c.relname AS "Name",
        CASE c.relkind
          WHEN ''r'' THEN pg_catalog.pg_gettext(''table'')
          WHEN ''v'' THEN pg_catalog.pg_gettext(''view'')
          WHEN ''i'' THEN pg_catalog.pg_gettext(''index'')
          WHEN ''s'' THEN pg_catalog.pg_gettext(''sequence'')
          WHEN ''S'' THEN pg_catalog.pg_gettext(''special'')
        END AS "Type",
        u.usename AS "Owner",
        c2.relname AS "Table"
   FROM (SELECT $1 AS cmd) AS cmd,
        pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
        LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
        LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE ((cmd.cmd ~ ''t'' AND c.relkind = ''r'')
        OR (cmd.cmd ~ ''i'' AND c.relkind = ''i'')
        OR (cmd.cmd ~ ''v'' AND c.relkind = ''v'')
        OR (cmd.cmd ~ ''s'' AND c.relkind = ''s''))
    AND pg_catalog.pg_table_is_visible(c.oid)
    AND ((cmd.cmd ~ ''S'' AND n.nspname = ''pg_catalog'')
        OR n.nspname NOT IN (''pg_catalog'', ''pg_toast''))
  ORDER BY n.nspname,c.relname', NULL);

--
-- \d[tivsS]*[+]
--
-- List of relations
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^d[tivsS]*[+]$', 'List of relations',
'SELECT n.nspname AS "Schema",
        c.relname AS "Name",
        CASE c.relkind
          WHEN ''r'' THEN pg_catalog.pg_gettext(''table'')
          WHEN ''v'' THEN pg_catalog.pg_gettext(''view'')
          WHEN ''i'' THEN pg_catalog.pg_gettext(''index'')
          WHEN ''s'' THEN pg_catalog.pg_gettext(''sequence'')
          WHEN ''S'' THEN pg_catalog.pg_gettext(''special'')
        END AS "Type",
        u.usename AS "Owner",
        c2.relname AS "Table"
        pg_catalog.obj_description(p.oid, ''pg_class'') as "Description" AS "Description"
   FROM (SELECT $1 AS cmd) AS cmd,
        pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
        LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
        LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE ((cmd.cmd ~ ''t'' AND c.relkind = ''r'')
        OR (cmd.cmd ~ ''i'' AND c.relkind = ''i'')
        OR (cmd.cmd ~ ''v'' AND c.relkind = ''v'')
        OR (cmd.cmd ~ ''s'' AND c.relkind = ''s''))
    AND pg_catalog.pg_table_is_visible(c.oid)
    AND ((cmd.cmd ~ ''S'' AND n.nspname = ''pg_catalog'')
        OR n.nspname NOT IN (''pg_catalog'', ''pg_toast''))
  ORDER BY 1,2', NULL);

--
-- \d[tivsS]+ <arg>
--
-- List of relations
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^d[tivsS]+$', 'List of relations',
'SELECT n.nspname AS "Schema",
        c.relname AS "Name",
        CASE c.relkind
          WHEN ''r'' THEN pg_catalog.pg_gettext(''table'')
          WHEN ''v'' THEN pg_catalog.pg_gettext(''view'')
          WHEN ''i'' THEN pg_catalog.pg_gettext(''index'')
          WHEN ''s'' THEN pg_catalog.pg_gettext(''sequence'')
          WHEN ''S'' THEN pg_catalog.pg_gettext(''special'')
        END AS "Type",
        u.usename AS "Owner",
        c2.relname AS "Table"
   FROM (SELECT $1 AS cmd) AS cmd,
        pg_catalog.pg_name_pattern($2) AS (schpat text, relpat text),
        pg_catalog.pg_namespace n,
        pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
        LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
        LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
  WHERE c.relnamespace = n.oid
    AND ((cmd.cmd ~ ''t'' AND c.relkind = ''r'')
        OR (cmd.cmd ~ ''i'' AND c.relkind = ''i'')
        OR (cmd.cmd ~ ''v'' AND c.relkind = ''v'')
        OR (cmd.cmd ~ ''s'' AND c.relkind = ''s''))
    AND n.nspname ~ schpat
    AND c.relname ~ relpat
  ORDER BY 1,2', NULL);

--
-- \d[tivsS]+[+] <arg>
--
-- List of relations
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^d[tivsS]+[+]$', 'List of relations',
'SELECT n.nspname AS "Schema",
        c.relname AS "Name",
        CASE c.relkind
          WHEN ''r'' THEN pg_catalog.pg_gettext(''table'')
          WHEN ''v'' THEN pg_catalog.pg_gettext(''view'')
          WHEN ''i'' THEN pg_catalog.pg_gettext(''index'')
          WHEN ''s'' THEN pg_catalog.pg_gettext(''sequence'')
          WHEN ''S'' THEN pg_catalog.pg_gettext(''special'')
        END AS "Type",
        u.usename AS "Owner",
        c2.relname AS "Table"
        pg_catalog.obj_description(p.oid, ''pg_class'') as "Description" AS "Description"
   FROM (SELECT $1 AS cmd) AS cmd,
        pg_catalog.pg_name_pattern($2) AS (schpat text, relpat text),
        pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
        LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
        LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE ((cmd.cmd ~ ''t'' AND c.relkind = ''r'')
        OR (cmd.cmd ~ ''i'' AND c.relkind = ''i'')
        OR (cmd.cmd ~ ''v'' AND c.relkind = ''v'')
        OR (cmd.cmd ~ ''s'' AND c.relkind = ''s''))
    AND n.nspname ~ schpat
    AND c.relname ~ relpat
  ORDER BY 1,2', NULL);

--
-- \da[+]?
--
-- List of aggregate functions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^da[+]?$', 'List of aggregate functions', 
' SELECT n.nspname as "Schema",
         p.proname AS "Name",
         CASE p.proargtypes[0]
           WHEN ''pg_catalog."any"''::pg_catalog.regtype
           THEN CAST(pg_catalog.pg_gettext(''(all types)'') AS pg_catalog.text)
           ELSE pg_catalog.format_type(p.proargtypes[0], NULL)
         END AS "Data type",
         pg_catalog.obj_description(p.oid, ''pg_proc'') as "Description"
    FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
         (SELECT $1 AS cmd) AS cmd
   WHERE p.proisagg
     AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 3', NULL);

--
-- \da[+]? <arg>
--
-- List of aggregate functions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^da[+]?$', 'List of aggregate functions', 
' SELECT n.nspname as "Schema",
         p.proname AS "Name",
         CASE p.proargtypes[0]
           WHEN ''pg_catalog."any"''::pg_catalog.regtype
           THEN CAST(pg_catalog.pg_gettext(''(all types)'') AS pg_catalog.text)
           ELSE pg_catalog.format_type(p.proargtypes[0], NULL)
         END AS "Data type",
         pg_catalog.obj_description(p.oid, ''pg_proc'') as "Description"
    FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
         (SELECT $1 AS cmd) AS cmd,
         pg_catalog.pg_name_pattern($2) AS (schpat text, aggpat text)
   WHERE p.proisagg
     AND n.nspname ~ schpat
     AND p.proname ~ aggpat
ORDER BY 1, 2, 3', NULL);

--
-- \dc
--
-- List of conversions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dc$', 'List of conversions', 
'SELECT n.nspname AS "Schema",
       c.conname AS "Name",
       pg_catalog.pg_encoding_to_char(c.conforencoding) AS "Source",
       pg_catalog.pg_encoding_to_char(c.contoencoding) AS "Destination",
       CASE WHEN c.condefault THEN pg_catalog.pg_gettext(''yes'')
       ELSE pg_catalog.pg_gettext(''no'') END AS "Default?"
  FROM pg_catalog.pg_conversion c,
       pg_catalog.pg_namespace n,
       (SELECT $1 AS cmd) AS cmd
 WHERE n.oid = c.connamespace
   AND pg_catalog.pg_conversion_is_visible(c.oid)
 ORDER BY 1, 2', NULL);

--
-- \dc <arg>
--
-- List of conversions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^dc$', 'List of conversions', 
'SELECT n.nspname AS "Schema",
       c.conname AS "Name",
       pg_catalog.pg_encoding_to_char(c.conforencoding) AS "Source",
       pg_catalog.pg_encoding_to_char(c.contoencoding) AS "Destination",
       CASE WHEN c.condefault THEN pg_catalog.pg_gettext(''yes'')
       ELSE pg_catalog.pg_gettext(''no'') END AS "Default?"
FROM pg_catalog.pg_conversion c,
     pg_catalog.pg_namespace n,
     pg_catalog.pg_name_pattern($2) AS (schpat text, conpat text),
     (SELECT $1 AS cmd) AS cmd
WHERE n.oid = c.connamespace
      AND n.nspname ~ schpat
      AND c.conname ~ conpat
ORDER BY 1, 2', NULL);

--
-- \dC
--
-- List of casts
--
-- NEED LEFT JOIN FOR BINARY CASTS
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dC$', 'List of casts', 
'SELECT pg_catalog.format_type(castsource, NULL) AS "Source",
       pg_catalog.format_type(casttarget, NULL) AS "Target",
       CASE WHEN castfunc = 0 THEN pg_catalog.pg_gettext(''BINARY'')
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = ''e'' THEN pg_catalog.pg_gettext(''no'')
            WHEN c.castcontext = ''a'' THEN pg_catalog.pg_gettext(''in assignment'')
            ELSE pg_catalog.pg_gettext(''yes'')
       END as "Implicit?"
FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid,
     (SELECT $1 AS cmd) AS cmd
ORDER BY 1, 2', NULL);

--
-- \dd
--
-- Object descriptions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dd$', 'Object descriptions', 
'SELECT DISTINCT tt.nspname AS "Schema",
                tt.name AS "Name",
                tt.object AS "Object",
                d.description AS "Description"
FROM (
-- Aggregate description
  SELECT p.oid as oid, p.tableoid as tableoid,
  n.nspname as nspname,
  CAST(p.proname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''aggregate'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
      (SELECT $1 AS cmd) AS cmd
  WHERE p.proisagg
    AND pg_catalog.pg_function_is_visible(p.oid)

-- Function descriptions
UNION ALL
  SELECT p.oid as oid, p.tableoid as tableoid,
  n.nspname as nspname,
  CAST(p.proname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''function'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  WHERE p.prorettype <> ''pg_catalog.cstring''::pg_catalog.regtype
      AND p.proargtypes[0] <> ''pg_catalog.cstring''::pg_catalog.regtype
      AND NOT p.proisagg
      AND pg_catalog.pg_function_is_visible(p.oid)

-- Operator descriptions (only if operator has its own comment)
UNION ALL
  SELECT o.oid as oid, o.tableoid as tableoid,
  n.nspname as nspname,
  CAST(o.oprname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''operator'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_operator o
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
  WHERE pg_catalog.pg_operator_is_visible(o.oid)

-- Type description
UNION ALL
  SELECT t.oid as oid, t.tableoid as tableoid,
  n.nspname as nspname,
  pg_catalog.format_type(t.oid, NULL) as name,
  CAST(pg_catalog.pg_gettext(''data type'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_type t
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
  WHERE pg_catalog.pg_type_is_visible(t.oid)

-- Relation (tables, views, indexes, sequences) descriptions
UNION ALL
  SELECT c.oid as oid, c.tableoid as tableoid,
  n.nspname as nspname,
  CAST(c.relname AS pg_catalog.text) as name,
  CAST(
    CASE c.relkind WHEN ''r'' THEN pg_catalog.pg_gettext(''table'')
                   WHEN ''v'' THEN pg_catalog.pg_gettext(''view'')
                   WHEN ''i'' THEN pg_catalog.pg_gettext(''index'')
                   WHEN ''S'' THEN pg_catalog.pg_gettext(''sequence'')
    END AS pg_catalog.text
  ) as object
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN (''r'', ''v'', ''i'', ''S'')
    AND pg_catalog.pg_table_is_visible(c.oid)

-- Rule description (ignore rules for views)
UNION ALL
  SELECT r.oid as oid, r.tableoid as tableoid,
  n.nspname as nspname,
  CAST(r.rulename AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''rule'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_rewrite r
       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE r.rulename != ''_RETURN''
    AND pg_catalog.pg_table_is_visible(c.oid)

-- Trigger description
UNION ALL
  SELECT t.oid as oid, t.tableoid as tableoid,
  n.nspname as nspname,
  CAST(t.tgname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''trigger'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_trigger t
       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE pg_catalog.pg_table_is_visible(c.oid)
) AS tt
  JOIN pg_catalog.pg_description d
       ON (tt.oid = d.objoid and tt.tableoid = d.classoid and d.objsubid = 0)
ORDER BY 1, 2, 3', NULL);

--
-- \dd <arg>
--
-- Object descriptions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^dd$', 'Object descriptions', 
'SELECT DISTINCT tt.nspname AS "Schema",
                tt.name AS "Name",
                tt.object AS "Object",
                d.description AS "Description"
FROM (
-- Aggregate description
  SELECT p.oid as oid, p.tableoid as tableoid,
  n.nspname as nspname,
  CAST(p.proname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''aggregate'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, propat text)
  WHERE p.proisagg
    AND n.nspname ~ schpat
    AND p.proname ~ propat

-- Function descriptions
UNION ALL
  SELECT p.oid as oid, p.tableoid as tableoid,
  n.nspname as nspname,
  CAST(p.proname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''function'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, propat text)
  WHERE p.prorettype <> ''pg_catalog.cstring''::pg_catalog.regtype
    AND p.proargtypes[0] <> ''pg_catalog.cstring''::pg_catalog.regtype
    AND NOT p.proisagg
    AND n.nspname ~ schpat
    AND p.proname ~ propat  

-- Operator descriptions (only if operator has its own comment)
UNION ALL
  SELECT o.oid as oid, o.tableoid as tableoid,
  n.nspname as nspname,
  CAST(o.oprname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''operator'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_operator o
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, oprpat text)
  WHERE n.nspname ~ schpat
    AND o.oprname ~ oprpat

-- Type description
UNION ALL
  SELECT t.oid as oid, t.tableoid as tableoid,
  n.nspname as nspname,
  pg_catalog.format_type(t.oid, NULL) as name,
  CAST(pg_catalog.pg_gettext(''data type'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_type t
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, typpat text)
  WHERE n.nspname ~ schpat
    AND pg_catalog.format_type(t.oid, NULL) ~ typpat

-- Relation (tables, views, indexes, sequences) descriptions
UNION ALL
  SELECT c.oid as oid, c.tableoid as tableoid,
  n.nspname as nspname,
  CAST(c.relname AS pg_catalog.text) as name,
  CAST(
    CASE c.relkind WHEN ''r'' THEN pg_catalog.pg_gettext(''table'')
                   WHEN ''v'' THEN pg_catalog.pg_gettext(''view'')
                   WHEN ''i'' THEN pg_catalog.pg_gettext(''index'')
                   WHEN ''S'' THEN pg_catalog.pg_gettext(''sequence'')
    END AS pg_catalog.text
  ) as object
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, relpat text)
  WHERE c.relkind IN (''r'', ''v'', ''i'', ''S'')
    AND n.nspname ~ schpat
    AND c.relname ~ relpat

-- Rule description (ignore rules for views)
UNION ALL
  SELECT r.oid as oid, r.tableoid as tableoid,
  n.nspname as nspname,
  CAST(r.rulename AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''rule'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_rewrite r
       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, rlepat text)
  WHERE r.rulename != ''_RETURN''
    AND n.nspname ~ schpat
    AND r.rulename ~ rlepat

-- Trigger description
UNION ALL
  SELECT t.oid as oid, t.tableoid as tableoid,
  n.nspname as nspname,
  CAST(t.tgname AS pg_catalog.text) as name,
  CAST(pg_catalog.pg_gettext(''trigger'') AS pg_catalog.text) as object
  FROM pg_catalog.pg_trigger t
       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, tgrpat text)
  WHERE n.nspname ~ schpat
    AND t.tgname ~ tgrpat
) AS tt
  JOIN pg_catalog.pg_description d
       ON (tt.oid = d.objoid and tt.tableoid = d.classoid and d.objsubid = 0),
  (SELECT $1 AS cmd) AS cmd
ORDER BY 1, 2, 3', NULL);

--
-- \dD
--
-- List of domains
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dD$', 'List of domains', 
'SELECT n.nspname as "Schema",
       t.typname as "Name",
       pg_catalog.format_type(t.typbasetype, t.typtypmod) as "Type",
       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN ''not null default ''||t.typdefault
            WHEN t.typnotnull AND t.typdefault IS NULL THEN ''not null''
            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN ''default ''||t.typdefault
            ELSE ''''
       END as "Modifier"
  FROM pg_catalog.pg_type t
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
       (SELECT $1 AS cmd) AS cmd
 WHERE t.typtype = ''d''
   AND pg_catalog.pg_type_is_visible(t.oid)
 ORDER BY 1, 2', NULL);

--
-- \dD <arg>
--
-- List of domains
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^dD$', 'List of domains', 
'SELECT n.nspname as "Schema",
       t.typname as "Name",
       pg_catalog.format_type(t.typbasetype, t.typtypmod) as "Type",
       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN ''not null default ''||t.typdefault
            WHEN t.typnotnull AND t.typdefault IS NULL THEN ''not null''
            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN ''default ''||t.typdefault
            ELSE ''''
       END as "Modifier"
  FROM pg_catalog.pg_type t
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
       pg_catalog.pg_name_pattern($2) AS (schpat text, dompat text),
       (SELECT $1 AS cmd) AS cmd
 WHERE t.typtype = ''d''
   AND n.nspname ~ schpat
   AND t.typname ~ dompat
 ORDER BY 1, 2', NULL);

--
-- \df
--
-- List of functions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^df$', 'List of functions', 
'SELECT CASE WHEN p.proretset THEN ''setof '' ELSE '''' END ||
         pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
       n.nspname as "Schema",
       p.proname as "Name",
       pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types"
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
       (SELECT $1 AS cmd) AS cmd
 WHERE p.prorettype <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND p.proargtypes[0] <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND NOT p.proisagg
   AND pg_catalog.pg_function_is_visible(p.oid)
 ORDER BY 2, 3, 1, 4', NULL);

--
-- \df[+]
--
-- List of functions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^df[+]$', 'List of functions', 
'SELECT CASE WHEN p.proretset THEN ''setof '' ELSE '''' END ||
         pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
       n.nspname as "Schema",
       p.proname as "Name",
       pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types",
       u.usename as "Owner",
       l.lanname as "Language",
       p.prosrc as "Source code",
       pg_catalog.obj_description(p.oid, ''pg_proc'') as "Description"
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
       LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
       LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner,
       (SELECT $1 AS cmd) AS cmd
 WHERE p.prorettype <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND p.proargtypes[0] <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND NOT p.proisagg
   AND pg_catalog.pg_function_is_visible(p.oid)
 ORDER BY 2, 3, 1, 4', NULL);

--
-- \df <arg>
--
-- List of functions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^df$', 'List of functions', 
'SELECT CASE WHEN p.proretset THEN ''setof '' ELSE '''' END ||
         pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
       n.nspname as "Schema",
       p.proname as "Name",
       pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types"
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace,
       (SELECT $1 AS cmd) AS cmd,
       pg_catalog.pg_name_pattern($2) AS (schpat text, funcpat text)
 WHERE p.prorettype <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND p.proargtypes[0] <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND NOT p.proisagg
   AND n.nspname ~ schpat
   AND p.proname ~ funcpat
 ORDER BY 2, 3, 1, 4', NULL);

--
-- \df[+] <arg>
--
-- List of functions
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^df[+]$', 'List of functions',
'SELECT CASE WHEN p.proretset THEN ''setof '' ELSE '''' END ||
       pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
       n.nspname as "Schema",
       p.proname as "Name",
       pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types",
       u.usename as "Owner",
       l.lanname as "Language",
       p.prosrc as "Source code",
       pg_catalog.obj_description(p.oid, ''pg_proc'') as "Description"
  FROM pg_catalog.pg_proc p
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
       LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
       LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner,
       (SELECT $1 AS cmd) AS cmd,
       pg_catalog.pg_name_pattern($2) AS (schpat text, funcpat text)
 WHERE p.prorettype <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND p.proargtypes[0] <> ''pg_catalog.cstring''::pg_catalog.regtype
   AND NOT p.proisagg
   AND n.nspname ~ schpat
   AND p.proname ~ funcpat
 ORDER BY 2, 3, 1, 4', NULL);
 
--
-- \(dl|l|PSQL-databaselist)
--
-- List of databases
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^(dl|l|PSQL-databaselist)$', 'List of databases',
'SELECT d.datname as "Name",
       u.usename as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
     LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1', NULL);

--
-- \(dl|l|PSQL-databaselist)[+]
--
-- List of databases
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^(dl|l|PSQL-databaselist)[+]$', 'List of databases',
'SELECT d.datname as "Name",
       u.usename as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       pg_catalog.obj_description(d.oid, ''pg_database'') as "Description"
FROM pg_catalog.pg_database d
     LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1', NULL);

--
-- \dn
--
-- List of schemas
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dn$', 'List of schemas', 
'SELECT n.nspname AS "Name",
       u.usename AS "Owner"
FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u
       ON n.nspowner=u.usesysid,
     (SELECT $1 AS cmd) AS cmd
ORDER BY 1', NULL);

--
-- \dn <arg>
--
-- List of schemas
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^dn$', 'List of schemas', 
'SELECT n.nspname AS "Name",
       u.usename AS "Owner"
FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u
       ON n.nspowner=u.usesysid,
     (SELECT $1 AS cmd) AS cmd,
     pg_catalog.pg_name_pattern($2) AS (schpat text, nsppat text)
WHERE n.nspname ~ nsppat
ORDER BY 1', NULL);

--
-- \do[+]?
--
-- List of operators
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^do[+]?$', 'List of operators', 
'SELECT n.nspname as "Schema",
       o.oprname AS "Name",
       CASE WHEN o.oprkind=''l'' THEN NULL
            ELSE pg_catalog.format_type(o.oprleft, NULL)
       END AS "Left arg type",
       CASE WHEN o.oprkind=''r'' THEN NULL
            ELSE pg_catalog.format_type(o.oprright, NULL)
       END AS "Right arg type",
       pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
       coalesce(pg_catalog.obj_description(o.oid, ''pg_operator''),
                pg_catalog.obj_description(o.oprcode, ''pg_proc'')) AS "Description"
FROM pg_catalog.pg_operator o
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace,
     (SELECT $1 AS cmd) AS cmd
WHERE pg_catalog.pg_operator_is_visible(o.oid)
ORDER BY 1, 2, 3, 4', NULL);

--
-- \do[+]? <arg>
--
-- List of operators
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^do[+]?$', 'List of operators', 
'SELECT n.nspname as "Schema",
       o.oprname AS "Name",
       CASE WHEN o.oprkind=''l'' THEN NULL
            ELSE pg_catalog.format_type(o.oprleft, NULL)
       END AS "Left arg type",
       CASE WHEN o.oprkind=''r'' THEN NULL
            ELSE pg_catalog.format_type(o.oprright, NULL)
       END AS "Right arg type",
       pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
       coalesce(pg_catalog.obj_description(o.oid, ''pg_operator''),
                pg_catalog.obj_description(o.oprcode, ''pg_proc'')) AS "Description"
FROM pg_catalog.pg_operator o
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace,
     (SELECT $1 AS cmd) AS cmd,
     pg_catalog.pg_name_pattern($2) AS (schpat text, oprpat text)
WHERE pg_catalog.pg_operator_is_visible(o.oid)
      AND n.nspname ~ schpat
      AND o.oprname ~ oprpat
ORDER BY 1, 2, 3, 4', NULL);

--
-- \dp or \z
--
-- Access privileges
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^(dp|z)$', 'Access privileges', 
'SELECT n.nspname as "Schema",
       c.relname as "Table",
       c.relacl as "Access Privileges"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
     (SELECT $1 AS cmd) AS cmd
WHERE c.relkind IN (''r'', ''v'', ''S'')
  AND pg_catalog.pg_table_is_visible(c.oid)
  AND n.nspname !~ ''^pg_''
ORDER BY 1, 2', NULL);

--
-- \dp <arg> or \z <arg>
--
-- Access privileges
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^(dp|z)$', 'Access privileges',
'SELECT n.nspname as "Schema",
       c.relname as "Table",
       c.relacl as "Access Privileges"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
     (SELECT $1 AS cmd) AS cmd,
     pg_catalog.pg_name_pattern($2) AS (schpat text, nsppat text)
WHERE c.relkind IN (''r'', ''v'', ''S'')
  AND n.nspname ~ schpat
  AND c.relname ~ nsppat
ORDER BY 1, 2', NULL);

--
-- \dT
--
-- List of data types
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dT$', 'List of data types',
'SELECT n.nspname as "Schema",
       pg_catalog.format_type(t.oid, NULL) AS "Name",
       pg_catalog.obj_description(t.oid, ''pg_type'') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
     (SELECT $1 AS cmd) AS cmd
WHERE (t.typrelid = 0
      OR (SELECT c.relkind = ''c''
            FROM pg_catalog.pg_class c
           WHERE c.oid = t.typrelid))
      AND t.typname !~ ''^_''
      AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2', NULL);

--
-- \dT[+]
--
-- Verbose listing of data types
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^dT[+]$', 'List of data types',
'SELECT n.nspname as "Schema",
       pg_catalog.format_type(t.oid, NULL) AS "Name",
       t.typname AS "Internal name",
       CASE WHEN t.typrelid != 0
              THEN CAST(''tuple'' AS pg_catalog.text)
            WHEN t.typlen < 0
              THEN CAST(''var'' AS pg_catalog.text)
            ELSE CAST(t.typlen AS pg_catalog.text)
       END AS "Size",
       pg_catalog.obj_description(t.oid, ''pg_type'') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
     (SELECT $1 AS cmd) AS cmd
WHERE (t.typrelid = 0
      OR (SELECT c.relkind = ''c''
            FROM pg_catalog.pg_class c
           WHERE c.oid = t.typrelid))
      AND t.typname !~ ''^_''
      AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2', NULL);

--
-- \dT <arg>
--
-- List of data types
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^dT$', 'List of data types',
'SELECT n.nspname as "Schema",
       pg_catalog.format_type(t.oid, NULL) AS "Name",
       pg_catalog.obj_description(t.oid, ''pg_type'') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
     (SELECT $1 AS cmd) AS cmd,
     pg_catalog.pg_name_pattern($2) AS (schpat text, typpat text)
WHERE (t.typrelid = 0
      OR (SELECT c.relkind = ''c''
            FROM pg_catalog.pg_class c
           WHERE c.oid = t.typrelid))
      AND n.nspname ~ schpat
      AND (t.typname ~ typpat
          OR pg_catalog.format_type(t.oid, NULL) ~ typpat)
ORDER BY 1, 2', NULL);

--
-- \dT[+] <arg>
--
-- List of data types
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^dT[+]$', 'List of data types',
'SELECT n.nspname as "Schema",
       pg_catalog.format_type(t.oid, NULL) AS "Name",
       t.typname AS "Internal name",
       CASE WHEN t.typrelid != 0
              THEN CAST(''tuple'' AS pg_catalog.text)
            WHEN t.typlen < 0
              THEN CAST(''var'' AS pg_catalog.text)
            ELSE CAST(t.typlen AS pg_catalog.text)
       END AS "Size",
       pg_catalog.obj_description(t.oid, ''pg_type'') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace,
     (SELECT $1 AS cmd) AS cmd,
     pg_catalog.pg_name_pattern($2) AS (schpat text, typpat text)
WHERE (t.typrelid = 0
      OR (SELECT c.relkind = ''c''
            FROM pg_catalog.pg_class c
           WHERE c.oid = t.typrelid))
      AND n.nspname ~ schpat
      AND (t.typname ~ typpat
       OR pg_catalog.format_type(t.oid, NULL) ~ typpat)
ORDER BY 1, 2', NULL);

--
-- \du
--
-- List of database users
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 0, '^du$', 'List of database users', 
'SELECT u.usename AS "User name",
       u.usesysid AS "User ID",
       CASE WHEN u.usesuper AND u.usecreatedb THEN CAST(pg_catalog.pg_gettext(''superuser, create database'') AS pg_catalog.text)
            WHEN u.usesuper THEN CAST(pg_catalog.pg_gettext(''superuser'') AS pg_catalog.text)
            WHEN u.usecreatedb THEN CAST(pg_catalog.pg_gettext(''create database'') AS pg_catalog.text)
            ELSE CAST('''' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u,
     (SELECT $1 AS cmd) AS cmd
ORDER BY 1', NULL);

--
-- \du <arg>
--
-- List of database users
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^du$', 'List of database users', 
'SELECT u.usename AS "User name",
       u.usesysid AS "User ID",
       CASE WHEN u.usesuper AND u.usecreatedb THEN CAST(pg_catalog.pg_gettext(''superuser, create database'') AS pg_catalog.text)
            WHEN u.usesuper THEN CAST(pg_catalog.pg_gettext(''superuser'') AS pg_catalog.text)
            WHEN u.usecreatedb THEN CAST(pg_catalog.pg_gettext(''create database'') AS pg_catalog.text)
            ELSE CAST('''' AS pg_catalog.text)
       END AS "Attributes"
FROM pg_catalog.pg_user u,
     (SELECT $1 AS cmd) AS cmd,
     pg_catalog.pg_name_pattern($2) AS (schpat text, userpat text)
WHERE u.usename ~ userpat
ORDER BY 1', NULL);

--
-- PSQL-onerelation <arg>
--
-- Relation definition
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^PSQL-onerelation$', 'Relation definition -- as used by PSQL',
'SELECT tab.colname AS "Column",
       tab.coltype AS "Type",
       CASE WHEN tab.attnotnull THEN ''not null ''
            ELSE CAST('''' AS text)
       END ||
       CASE
         WHEN atthasdef THEN
           ''default '' || 
           (SELECT substring(d.adsrc for 128)
              FROM pg_catalog.pg_attrdef d
             WHERE d.adrelid = tab.attrelid
               AND d.adnum = tab.attnum) || '' ''
         ELSE CAST('''' AS text)
       END AS "Modifiers"
  FROM ( SELECT CASE 
                  WHEN i.indproc IS NULL THEN a.attname
                  WHEN i.indproc = (''-''::pg_catalog.regproc) THEN a.attname
                  ELSE SUBSTR(pg_catalog.pg_get_indexdef(attrelid),
                              POSITION(''('' IN pg_catalog.pg_get_indexdef(attrelid))) 
                END AS colname,
                pg_catalog.format_type(a.atttypid, a.atttypmod) as coltype,
                a.attnotnull,
                a.atthasdef,
                a.attnum,
                a.attrelid
           FROM pg_catalog.pg_attribute a
                LEFT OUTER JOIN pg_catalog.pg_index i ON (a.attrelid = i.indexrelid)
          WHERE a.attrelid = $2
                AND a.attnum > 0
                AND NOT a.attisdropped
       ORDER BY a.attnum
       ) as tab,
       (SELECT $1 AS cmd) AS cmd',
-- Footnotes for PSQL-onerelation
'SELECT ''View definition'' AS key,
       pg_catalog.pg_get_viewdef(c.oid) AS value
  FROM pg_catalog.pg_class c,
       (SELECT $1 AS cmd) AS cmd
 WHERE c.oid = $2 AND relkind = ''v''
 UNION ALL
SELECT ''Index'' AS key,
       CASE WHEN tab.indisprimary THEN ''primary key ''
            WHEN tab.indisunique THEN ''unique ''
            ELSE ''''
       END ||
       SUBSTRING(tab.indexdef FROM POSITION(''USING'' IN tab.indexdef) + 6) AS value
  FROM (SELECT i.indisprimary,
               i.indisunique,
               pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef
          FROM pg_catalog.pg_class c,
               pg_catalog.pg_index i
         WHERE c.oid = i.indrelid
              AND c.oid = $2 
       ) as tab
 UNION ALL
SELECT ''Rules'' AS key,
       r.rulename AS value
  FROM pg_catalog.pg_class c,
       pg_catalog.pg_rewrite r
 WHERE r.ev_class = c.oid
   AND c.oid = $2
   AND (c.relkind != ''v'' OR r.rulename != ''_RETURN'')
 UNION ALL
SELECT CASE contype
         WHEN ''c'' THEN ''Check constraints''
         WHEN ''f'' THEN ''Foreign Key constraints''
       END AS key,
       CASE contype
         WHEN ''c'' THEN ''"'' || conname || ''" '' || consrc
         WHEN ''f'' THEN conname || '' '' || pg_catalog.pg_get_constraintdef(oid)
       END AS value
  FROM pg_catalog.pg_constraint r
 WHERE r.conrelid = $2
       AND r.contype IN (''c'', ''f'')
 UNION ALL
SELECT ''Triggers'' AS key,
       t.tgname AS value
  FROM pg_catalog.pg_trigger t
 WHERE t.tgrelid = $2
   and (NOT tgisconstraint
        OR NOT EXISTS
          (SELECT 1
             FROM pg_catalog.pg_depend d,
                  pg_catalog.pg_constraint c
            WHERE d.refclassid = c.tableoid
                  AND d.refobjid = c.oid
                  AND d.classid = t.tableoid
                  AND d.objid = t.oid
                  AND d.deptype = ''i''
                  AND c.contype = ''f''))');

--
-- PSQL-tablelist <arg>
--
-- List of tables, with schema.
--
INSERT INTO psql_commands
     VALUES (DEFAULT, 1, '^PSQL-tablelist$', 'PSQL INTERNAL COMMAND - List Tables', 
'SELECT c.oid,
       CASE c.relkind
         WHEN ''r'' THEN ''Table "'' || n.nspname || ''.'' || c.relname || ''"''
         WHEN ''v'' THEN ''View "'' || n.nspname || ''.'' || c.relname || ''"''
         WHEN ''S'' THEN ''Sequence "'' || n.nspname || ''.'' || c.relname || ''"''
         WHEN ''i'' THEN ''Index "'' || n.nspname || ''.'' || c.relname || ''"''
         WHEN ''s'' THEN ''Special relation "'' || n.nspname || ''.'' || c.relname || ''"''
         WHEN ''t'' THEN ''TOAST table "'' || n.nspname || ''.'' || c.relname || ''"''
         WHEN ''c'' THEN ''Composite type "'' || n.nspname || ''.'' || c.relname || ''"''
         ELSE c.relkind || '' "'' || n.nspname || ''.'' || c.relname || ''"''
       END AS title
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
       (SELECT $1 AS cmd) AS cmd,
       pg_catalog.pg_name_pattern($2) AS (schpat text, tabpat text)
 WHERE n.nspname ~ schpat
       AND c.relname ~ tabpat
 ORDER BY n.nspname, c.relname', NULL);
