DROP SCHEMA pgtools CASCADE;

BEGIN;
CREATE SCHEMA pgtools
 CREATE TABLE psqlcommands
 ( 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
 )
;

-- \dqtest
INSERT INTO pgtools.psqlcommands
     VALUES (DEFAULT, 0, 'dqtest', 'WOO HOO!', 
'SELECT 3, 4', NULL);

-- \dqdb
INSERT INTO pgtools.psqlcommands
     VALUES (DEFAULT, 0, 'dqdb', 'List 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 AS d
  LEFT JOIN pg_catalog.pg_user AS U ON (d.datdba = u.usesysid)
ORDER BY "Name"', NULL);

-- \dqdb <DBName>
INSERT INTO pgtools.psqlcommands
     VALUES (DEFAULT, 1, 'dqdb', 'List 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 AS d
  LEFT JOIN pg_catalog.pg_user AS U ON (d.datdba = u.usesysid)
  WHERE d.datname LIKE ''%'' || $1 || ''%''
ORDER BY "Name"', NULL);



COMMIT;

select * from pgtools.psqlcommands;