// Create various helper functions/tables

SELECT * INTO pgadmin_desc FROM pg_description WHERE objoid > 18655
CREATE TABLE pgadmin_seq_cache(sequence_oid oid, sequence_last_value int4, sequence_increment_by int4, sequence_max_value int4, sequence_min_value int4, sequence_cache_value int4, sequence_is_cycled text, sequence_timestamp timestamp DEFAULT now())
CREATE TABLE pgadmin_table_cache(table_oid oid, table_rows int4, table_timestamp timestamp DEFAULT now())
CREATE FUNCTION pgadmin_get_desc(oid) RETURNS text AS 'SELECT description FROM pgadmin_desc WHERE objoid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_pgdesc(oid) RETURNS text AS 'SELECT description FROM pg_description WHERE objoid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_col_def(oid, int4) RETURNS text AS 'SELECT adsrc FROM pg_attrdef WHERE adrelid = $1 AND adnum = $2' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_handler(oid) RETURNS text AS 'SELECT proname::text FROM pg_proc WHERE oid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_type(oid) RETURNS text AS 'SELECT typname::text FROM pg_type WHERE oid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_rows(oid) RETURNS pgadmin_table_cache AS 'SELECT DISTINCT ON(table_oid) * FROM pgadmin_table_cache WHERE table_oid = $1 ORDER BY table_oid, table_timestamp DESC' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_sequence(oid) RETURNS pgadmin_seq_cache AS 'SELECT DISTINCT ON(sequence_oid) * FROM pgadmin_seq_cache WHERE sequence_oid = $1 ORDER BY sequence_oid, sequence_timestamp DESC' LANGUAGE 'sql'

// Datbases

CREATE VIEW pgadmin_databases AS SELECT 
  d.oid AS database_oid, 
  d.datname AS database_name, 
  d.datpath AS database_path, 
  pg_get_userbyid(d.datdba) AS database_owner, 
  CASE WHEN d.oid <= 18655 THEN pgadmin_get_pgdesc(d.oid) ELSE pgadmin_get_desc(d.oid) END AS database_comments
FROM 
  pg_database d

// Functions (only lists up to 16 args - ideas welcome!!)
  
CREATE VIEW pgadmin_functions AS SELECT 
  p.oid AS function_oid, 
  p.proname AS function_name, 
  pg_get_userbyid(p.proowner) AS function_owner, 
  rtrim(trim(
    CASE WHEN (pgadmin_get_type(p.proargtypes[0]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[0]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[1]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[1]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[2]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[2]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[3]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[3]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[4]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[4]) || ', ' ELSE '' END ||
    CASE WHEN (pgadmin_get_type(p.proargtypes[5]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[5]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[6]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[6]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[7]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[7]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[8]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[8]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[9]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[9]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[10]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[10]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[11]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[11]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[12]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[12]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[13]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[13]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[14]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[14]) || ', ' ELSE '' END || 
    CASE WHEN (pgadmin_get_type(p.proargtypes[15]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[15]) || ', ' ELSE '' END
  ), ',') AS function_arguments,
  pgadmin_get_type(p.prorettype) AS function_returns, 
  p.prosrc AS function_source, l.lanname AS function_language,
  CASE WHEN p.oid <= 18655 THEN pgadmin_get_pgdesc(p.oid) ELSE pgadmin_get_desc(p.oid) END AS function_comments
FROM 
  pg_proc p, 
  pg_language l
WHERE 
  p.prolang = l.oid

// Indexes

CREATE VIEW pgadmin_indexes AS SELECT
  i.oid AS index_oid, 
  i.relname AS index_name, 
  c.relname AS index_table, 
  pg_get_userbyid(i.relowner) AS index_owner,
  CASE WHEN x.indislossy = TRUE THEN 'Yes'::text ELSE 'No'::text END AS index_is_lossy, 
  CASE WHEN x.indisunique = TRUE THEN 'Yes'::text ELSE 'No'::text END AS index_is_unique, 
  CASE WHEN x.indisprimary = TRUE THEN 'Yes'::text ELSE 'No'::text END AS index_is_primary,
  CASE WHEN i.oid <= 18655 THEN pgadmin_get_pgdesc(i.oid) ELSE pgadmin_get_desc(i.oid) END AS index_comments,
  pg_get_indexdef(x.indexrelid) AS index_definition, 
  a.oid AS column_oid, 
  a.attname AS column_name,  
  a.attnum AS column_position,  
  t.typname As column_type,
  CASE WHEN ((a.attlen = -1) AND ((a.atttypmod)::int4 = (-1)::int4)) THEN 
    (0)::int4 
  ELSE 
    CASE WHEN a.attlen = -1 THEN 
      CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char') OR (t.typname = 'varchar')) THEN 
        (a.atttypmod -4)::int4 
      ELSE 
        (a.atttypmod)::int4 
      END 
    ELSE 
      (a.attlen)::int4 
    END 
  END AS column_length,
  CASE WHEN a.oid <= 18655 THEN pgadmin_get_pgdesc(a.oid) ELSE pgadmin_get_desc(a.oid) END AS column_comments 
FROM 
  pg_index x, 
  pg_attribute a, 
  pg_type t, 
  pg_class c,
  pg_class i
WHERE 
  a.atttypid = t.oid AND 
  a.attrelid = i.oid AND 
  ((c.oid = x.indrelid) AND (i.oid = x.indexrelid))

// Languages

CREATE VIEW pgadmin_languages AS SELECT 
  l.oid AS language_oid, 
  l.lanname AS language_name, 
  l.lancompiler AS language_compiler, 
  CASE WHEN l.lanpltrusted = TRUE THEN 'Yes'::text ELSE 'No'::text END AS language_is_trusted, 
  pgadmin_get_handler(lanplcallfoid) AS language_handler, 
  CASE WHEN l.oid <= 18655 THEN pgadmin_get_pgdesc(l.oid) ELSE pgadmin_get_desc(l.oid) END AS language_comments
FROM 
  pg_language l

// Sequences (this currently relies on pgAdmin scanning all sequences and bunging info into pgadmin_seq_cache)

CREATE VIEW pgadmin_sequences AS SELECT 
  c.oid AS sequence_oid, 
  c.relname AS sequence_name, 
  pg_get_userbyid(c.relowner) AS sequence_owner, 
  sequence_last_value(pgadmin_get_sequence(c.oid)) AS sequence_last_value, 
  sequence_increment_by(pgadmin_get_sequence(c.oid)) AS sequence_increment_by, 
  sequence_max_value(pgadmin_get_sequence(c.oid)) AS sequence_max_value, 
  sequence_min_value(pgadmin_get_sequence(c.oid)) AS sequence_min_value, 
  sequence_cache_value(pgadmin_get_sequence(c.oid)) AS sequence_cache_value, 
  sequence_is_cycled(pgadmin_get_sequence(c.oid)) AS sequence_is_cycled,
  CASE WHEN c.oid <= 18655 THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS sequence_comments
FROM 
  pg_class c 
WHERE 
  c.relkind = 'S'

// Tables (this currently relies on pgAdmin creating a reliable record count for each table and putting it in pgadmin_table_cache)

CREATE VIEW pgadmin_tables AS SELECT 
  c.oid AS table_oid, 
  c.relname AS table_name, 
  pg_get_userbyid(c.relowner) AS table_owner, 
  c.relacl AS table_acl,
  CASE WHEN c.relhasindex = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_indexes, 
  CASE WHEN c.relhasrules = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_rules, 
  CASE WHEN c.relisshared = TRUE THEN 'Yes'::text ELSE 'No'::text END  AS table_is_shared, 
  CASE WHEN c.relhaspkey = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_primarykey, 
  CASE WHEN c.reltriggers > 0 THEN 'Yes'::text ELSE 'No'::text END AS table_has_triggers,
  table_rows(pgadmin_get_rows(c.oid)) AS table_rows, 
  CASE WHEN c.oid <= 18655 THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS table_comments, 
  a.oid AS column_oid, 
  a.attname AS column_name, 
  a.attnum AS column_position, 
  t.typname As column_type, 
  CASE WHEN ((a.attlen = -1) AND ((a.atttypmod)::int4 = (-1)::int4)) THEN 
    (0)::int4 
  ELSE 
    CASE WHEN a.attlen = -1 THEN 
      CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char') OR (t.typname = 'varchar')) THEN 
        (a.atttypmod -4)::int4 
      ELSE 
        (a.atttypmod)::int4 
      END 
    ELSE 
      (a.attlen)::int4 
    END 
  END AS column_length, 
  CASE WHEN a.attnotnull = TRUE THEN 'Yes'::text ELSE 'No'::text END AS column_not_null, 
  CASE WHEN a.atthasdef = TRUE THEN 'Yes'::text ELSE 'No'::text END AS column_has_default,  
  CASE WHEN (pgadmin_get_col_def(c.oid, a.attnum) NOTNULL) THEN pgadmin_get_col_def(c.oid, a.attnum) ELSE '' END AS column_default, 
  CASE WHEN a.oid <= 18655 THEN pgadmin_get_pgdesc(a.oid) ELSE pgadmin_get_desc(a.oid) END AS column_comments 
FROM 
  pg_attribute a, 
  pg_type t, 
  pg_class c
WHERE 
  a.atttypid = t.oid AND 
  a.attrelid = c.oid AND 
  (((c.relkind::char = 'r'::char) OR (c.relkind::char = 's'::char)) AND (NOT (EXISTS (SELECT pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND (pg_rewrite.ev_type::char = '1'::char))))))
  
// Triggers

CREATE VIEW pgadmin_triggers AS SELECT 
  t.oid AS trigger_oid, 
  t.tgname AS trigger_name, 
  c.relname AS trigger_table, 
  p.proname AS trigger_function, 
  t.tgtype AS trigger_type, 
  CASE WHEN t.oid <= 18655 THEN pgadmin_get_pgdesc(t.oid) ELSE pgadmin_get_desc(t.oid) END AS trigger_comments 
FROM 
  pg_trigger t, 
  pg_class c, 
  pg_proc p 
WHERE 
  c.oid = t.tgrelid AND 
  p.oid = tgfoid

// Views

CREATE VIEW pgadmin_views AS SELECT 
  c.oid AS view_oid, 
  c.relname AS view_name, 
  pg_get_userbyid(c.relowner) AS view_owner, 
  pg_get_viewdef(c.relname) AS view_definition, 
  CASE WHEN c.oid <= 18655 THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS view_comments 
FROM 
  pg_class c 
WHERE 
 (c.relhasrules AND (EXISTS (SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (r.ev_type::char = '1'::char)))))