[libgda/LIBGDA_4.0] PostgreSQL provider: avoid using the information schema



commit da055b5b09fe2fddf64c720768e2a32315f7f33d
Author: Vivien Malerba <malerba gnome-db org>
Date:   Mon Jul 27 20:47:31 2009 +0200

    PostgreSQL provider: avoid using the information schema
    
    Applied a patch from Bas Driessen which avoids using the
    information_schema.schemata and pg_catalog.pg_authid
    because of the roles checking and access restrictions
    respectively.

 providers/postgres/gda-postgres-meta.c |   26 +++++++++++++-------------
 1 files changed, 13 insertions(+), 13 deletions(-)
---
diff --git a/providers/postgres/gda-postgres-meta.c b/providers/postgres/gda-postgres-meta.c
index d27ad1c..0bd5cee 100644
--- a/providers/postgres/gda-postgres-meta.c
+++ b/providers/postgres/gda-postgres-meta.c
@@ -98,22 +98,22 @@ static gchar *internal_sql[] = {
 	"SELECT t.typname, 'pg_catalog.' || t.typname, 'gchararray', pg_catalog.obj_description(t.oid), NULL, CASE WHEN t.typname ~ '^_' THEN TRUE WHEN typtype = 'p' THEN TRUE WHEN t.typname in ('any', 'anyarray', 'anyelement', 'cid', 'cstring', 'int2vector', 'internal', 'language_handler', 'oidvector', 'opaque', 'record', 'refcursor', 'regclass', 'regoper', 'regoperator', 'regproc', 'regprocedure', 'regtype', 'SET', 'smgr', 'tid', 'trigger', 'unknown', 'void', 'xid', 'oid', 'aclitem') THEN TRUE ELSE FALSE END, CAST (t.oid AS int8) FROM pg_catalog.pg_type t, pg_catalog.pg_user u, pg_catalog.pg_namespace n WHERE t.typowner=u.usesysid AND n.oid = t.typnamespace AND pg_catalog.pg_type_is_visible(t.oid) AND (typtype='b' OR typtype='p') AND typelem = 0",
 
 	/* I_STMT_SCHEMAS */
-	"SELECT catalog_name, schema_name, schema_owner, CASE WHEN schema_name ~'^pg_' THEN TRUE WHEN schema_name ='information_schema' THEN TRUE ELSE FALSE END FROM information_schema.schemata WHERE catalog_name = ##cat::string",
+	"SELECT current_database()::information_schema.sql_identifier AS catalog_name, n.nspname::information_schema.sql_identifier AS schema_name, u.rolname::information_schema.sql_identifier AS schema_owner, CASE WHEN n.nspname::information_schema.sql_identifier ~ '^pg_' THEN TRUE WHEN n.nspname::information_schema.sql_identifier = 'information_schema' THEN TRUE ELSE FALSE END FROM pg_namespace n, pg_roles u WHERE n.nspowner = u.oid AND current_database()::information_schema.sql_identifier = ##cat::string",
 
 	/* I_STMT_SCHEMAS_ALL */
-	"SELECT catalog_name, schema_name, schema_owner, CASE WHEN schema_name ~'^pg_' THEN TRUE WHEN schema_name ='information_schema' THEN TRUE ELSE FALSE END FROM information_schema.schemata",
+	"SELECT current_database()::information_schema.sql_identifier AS catalog_name, n.nspname::information_schema.sql_identifier AS schema_name, u.rolname::information_schema.sql_identifier AS schema_owner, CASE WHEN n.nspname::information_schema.sql_identifier ~ '^pg_' THEN TRUE WHEN n.nspname::information_schema.sql_identifier = 'information_schema' THEN TRUE ELSE FALSE END FROM pg_namespace n, pg_roles u WHERE n.nspowner = u.oid",
 
 	/* I_STMT_SCHEMA_NAMED */
-	"SELECT catalog_name, schema_name, schema_owner, CASE WHEN schema_name ~'^pg_' THEN TRUE WHEN schema_name ='information_schema' THEN TRUE ELSE FALSE END FROM information_schema.schemata WHERE catalog_name = ##cat::string AND schema_name = ##name::string",
+	"SELECT current_database()::information_schema.sql_identifier AS catalog_name, n.nspname::information_schema.sql_identifier AS schema_name, u.rolname::information_schema.sql_identifier AS schema_owner, CASE WHEN n.nspname::information_schema.sql_identifier ~ '^pg_' THEN TRUE WHEN n.nspname::information_schema.sql_identifier = 'information_schema' THEN TRUE ELSE FALSE END FROM pg_namespace n, pg_roles u WHERE n.nspowner = u.oid AND SELECT current_database()::information_schema.sql_identifier = ##cat::string AND n.nspname::information_schema.sql_identifier = ##name::string",
 
 	/* I_STMT_TABLES */
-	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' ELSE NULL::text END::information_schema.character_data AS table_type, CASE WHEN c.relkind = 'r' THEN TRUE ELSE FALSE END, pg_catalog.obj_description(c.oid), CASE WHEN pg_catalog.pg_table_is_visible(c.oid) IS TRUE AND nc.nspname!='pg_catalog' THEN c.relname ELSE coalesce (nc.nspname || '.', '') || c.relname END, coalesce (nc.nspname || '.', '') || c.relname, o.rolname FROM pg_namespace nc, pg_class c, pg_authid o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND nc.nspname::information_schema.sql_identifier = ##schema::string AND c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r', 'v'])) AND NOT pg_is_ot
 her_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text)) AND o.oid=c.relowner",
+	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' ELSE NULL::text END::information_schema.character_data AS table_type, CASE WHEN c.relkind = 'r' THEN TRUE ELSE FALSE END, pg_catalog.obj_description(c.oid), CASE WHEN pg_catalog.pg_table_is_visible(c.oid) IS TRUE AND nc.nspname!='pg_catalog' THEN c.relname ELSE coalesce (nc.nspname || '.', '') || c.relname END, coalesce (nc.nspname || '.', '') || c.relname, o.rolname FROM pg_namespace nc, pg_class c, pg_roles o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND nc.nspname::information_schema.sql_identifier = ##schema::string AND c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r', 'v'])) AND NOT pg_is_oth
 er_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text)) AND o.oid=c.relowner",
 
 	/* I_STMT_TABLES_ALL */
-	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' ELSE NULL::text END::information_schema.character_data AS table_type, CASE WHEN c.relkind = 'r' THEN TRUE ELSE FALSE END, pg_catalog.obj_description(c.oid), CASE WHEN pg_catalog.pg_table_is_visible(c.oid) IS TRUE AND nc.nspname!='pg_catalog' THEN c.relname ELSE coalesce (nc.nspname || '.', '') || c.relname END, coalesce (nc.nspname || '.', '') || c.relname, o.rolname FROM pg_namespace nc, pg_class c, pg_authid o WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r', 'v'])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid,
  'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text)) AND o.oid=c.relowner",
+	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' ELSE NULL::text END::information_schema.character_data AS table_type, CASE WHEN c.relkind = 'r' THEN TRUE ELSE FALSE END, pg_catalog.obj_description(c.oid), CASE WHEN pg_catalog.pg_table_is_visible(c.oid) IS TRUE AND nc.nspname!='pg_catalog' THEN c.relname ELSE coalesce (nc.nspname || '.', '') || c.relname END, coalesce (nc.nspname || '.', '') || c.relname, o.rolname FROM pg_namespace nc, pg_class c, pg_roles o WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r', 'v'])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 
 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text)) AND o.oid=c.relowner",
 
 	/* I_STMT_TABLE_NAMED */
-	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' ELSE NULL::text END::information_schema.character_data AS table_type, CASE WHEN c.relkind = 'r' THEN TRUE ELSE FALSE END, pg_catalog.obj_description(c.oid), CASE WHEN pg_catalog.pg_table_is_visible(c.oid) IS TRUE AND nc.nspname!='pg_catalog' THEN c.relname ELSE coalesce (nc.nspname || '.', '') || c.relname END, coalesce (nc.nspname || '.', '') || c.relname, o.rolname FROM pg_namespace nc, pg_class c, pg_authid o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND nc.nspname::information_schema.sql_identifier = ##schema::string AND c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r', 'v'])) AND NOT pg_is_ot
 her_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text)) AND o.oid=c.relowner AND c.relname::information_schema.sql_identifier = ##name::string",
+	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' ELSE NULL::text END::information_schema.character_data AS table_type, CASE WHEN c.relkind = 'r' THEN TRUE ELSE FALSE END, pg_catalog.obj_description(c.oid), CASE WHEN pg_catalog.pg_table_is_visible(c.oid) IS TRUE AND nc.nspname!='pg_catalog' THEN c.relname ELSE coalesce (nc.nspname || '.', '') || c.relname END, coalesce (nc.nspname || '.', '') || c.relname, o.rolname FROM pg_namespace nc, pg_class c, pg_roles o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND nc.nspname::information_schema.sql_identifier = ##schema::string AND c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r', 'v'])) AND NOT pg_is_oth
 er_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text)) AND o.oid=c.relowner AND c.relname::information_schema.sql_identifier = ##name::string",
 
 	/* I_STMT_VIEWS */
 	"SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, pg_catalog.pg_get_viewdef(c.oid, TRUE), NULL, CASE WHEN c.relkind = 'r'::\"char\" THEN TRUE ELSE FALSE END FROM pg_namespace nc, pg_class c WHERE current_database()::information_schema.sql_identifier = ##cat::string AND nc.nspname::information_schema.sql_identifier = ##schema::string AND c.relnamespace = nc.oid AND c.relkind = 'v' AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'DELETE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text) OR has_table_privilege(c.oid, 'TRIGGER'::text))",
@@ -162,10 +162,10 @@ static gchar *internal_sql[] = {
 	"UNION SELECT current_database()::information_schema.sql_identifier AS table_catalog, nr.nspname::information_schema.sql_identifier AS table_schema, r.relname::information_schema.sql_identifier AS table_name, (((((nr.oid::text || '_'::text) || r.oid::text) || '_'::text) || a.attnum::text) || '_not_null'::text)::information_schema.sql_identifier AS constraint_name, a.attname FROM pg_namespace nr, pg_class r, pg_attribute a WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND a.attnotnull AND a.attnum > 0 AND NOT a.attisdropped AND r.relkind = 'r'::\"char\" AND NOT pg_is_other_temp_schema(nr.oid) AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'SELECT'::text) OR has_table_privilege(r.oid, 'INSERT'::text) OR has_table_privilege(r.oid, 'UPDATE'::text) OR has_table_privilege(r.oid, 'DELETE'::text) OR has_table_privilege(r.oid, 'REFERENCES'::text) OR has_table_privilege(r.oid, 'TRIGGER'::text))",
 
 	/* I_STMT_UDT */
-	"SELECT pg_catalog.current_database() as cat, n.nspname, t.typname, 'gchararray', pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (n.nspname || '.', '') || t.typname END, coalesce (n.nspname || '.', '') || t.typname, CASE WHEN t.typname ~ '^_' THEN TRUE WHEN t.typname in ('any', 'anyarray', 'anyelement', 'cid', 'cstring', 'int2vector', 'internal', 'language_handler', 'oidvector', 'opaque', 'record', 'refcursor', 'regclass', 'regoper', 'regoperator', 'regproc', 'regprocedure', 'regtype', 'SET', 'smgr', 'tid', 'trigger', 'unknown', 'void', 'xid', 'oid', 'aclitem') THEN TRUE ELSE FALSE END, o.rolname FROM pg_catalog.pg_type t, pg_catalog.pg_user u, pg_catalog.pg_namespace n , pg_authid o WHERE t.typowner=u.usesysid AND n.oid = t.typnamespace AND pg_catalog.pg_type_is_visible(t.oid) AND (t.typrelid != 0 AND (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND o.oid=t.typowner AND
  pg_catalog.current_database() = ##cat::string AND n.nspname = ##schema::string",
+	"SELECT pg_catalog.current_database() as cat, n.nspname, t.typname, 'gchararray', pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (n.nspname || '.', '') || t.typname END, coalesce (n.nspname || '.', '') || t.typname, CASE WHEN t.typname ~ '^_' THEN TRUE WHEN t.typname in ('any', 'anyarray', 'anyelement', 'cid', 'cstring', 'int2vector', 'internal', 'language_handler', 'oidvector', 'opaque', 'record', 'refcursor', 'regclass', 'regoper', 'regoperator', 'regproc', 'regprocedure', 'regtype', 'SET', 'smgr', 'tid', 'trigger', 'unknown', 'void', 'xid', 'oid', 'aclitem') THEN TRUE ELSE FALSE END, o.rolname FROM pg_catalog.pg_type t, pg_catalog.pg_user u, pg_catalog.pg_namespace n , pg_roles o WHERE t.typowner=u.usesysid AND n.oid = t.typnamespace AND pg_catalog.pg_type_is_visible(t.oid) AND (t.typrelid != 0 AND (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND o.oid=t.typowner AND 
 pg_catalog.current_database() = ##cat::string AND n.nspname = ##schema::string",
 
 	/* I_STMT_UDT_ALL */
-	"SELECT pg_catalog.current_database(), n.nspname, t.typname, 'gchararray', pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (n.nspname || '.', '') || t.typname END, coalesce (n.nspname || '.', '') || t.typname, CASE WHEN t.typname ~ '^_' THEN TRUE WHEN t.typname in ('any', 'anyarray', 'anyelement', 'cid', 'cstring', 'int2vector', 'internal', 'language_handler', 'oidvector', 'opaque', 'record', 'refcursor', 'regclass', 'regoper', 'regoperator', 'regproc', 'regprocedure', 'regtype', 'SET', 'smgr', 'tid', 'trigger', 'unknown', 'void', 'xid', 'oid', 'aclitem') THEN TRUE ELSE FALSE END, o.rolname FROM pg_catalog.pg_type t, pg_catalog.pg_user u, pg_catalog.pg_namespace n , pg_authid o WHERE t.typowner=u.usesysid AND n.oid = t.typnamespace AND pg_catalog.pg_type_is_visible(t.oid) AND (t.typrelid != 0 AND (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND o.oid=t.typowner",
+	"SELECT pg_catalog.current_database(), n.nspname, t.typname, 'gchararray', pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (n.nspname || '.', '') || t.typname END, coalesce (n.nspname || '.', '') || t.typname, CASE WHEN t.typname ~ '^_' THEN TRUE WHEN t.typname in ('any', 'anyarray', 'anyelement', 'cid', 'cstring', 'int2vector', 'internal', 'language_handler', 'oidvector', 'opaque', 'record', 'refcursor', 'regclass', 'regoper', 'regoperator', 'regproc', 'regprocedure', 'regtype', 'SET', 'smgr', 'tid', 'trigger', 'unknown', 'void', 'xid', 'oid', 'aclitem') THEN TRUE ELSE FALSE END, o.rolname FROM pg_catalog.pg_type t, pg_catalog.pg_user u, pg_catalog.pg_namespace n , pg_roles o WHERE t.typowner=u.usesysid AND n.oid = t.typnamespace AND pg_catalog.pg_type_is_visible(t.oid) AND (t.typrelid != 0 AND (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND o.oid=t.typowner",
 
 	/* I_STMT_UDT_COLUMNS */
 	"select pg_catalog.current_database(), n.nspname, udt.typname, a.attname, a.attnum, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN NULL ELSE coalesce (nt.nspname || '.', '') || t.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'UDT' || current_database() || '.' || n.nspname || '.' || udt.typname || '.' || a.attnum ELSE NULL END, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truety
 pmod(a.*, t.*)), NULL, NULL , NULL, NULL, NULL, NULL FROM pg_type udt INNER JOIN pg_namespace n ON (udt.typnamespace=n.oid) INNER JOIN pg_attribute a ON (a.attrelid=udt.typrelid) INNER JOIN pg_type t ON (a.atttypid=t.oid) INNER JOIN pg_namespace nt ON (t.typnamespace = nt.oid) WHERE udt.typrelid != 0 AND (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = udt.typrelid) AND pg_catalog.current_database() = ##cat::string AND n.nspname = ##schema::string AND udt.typname = ##name::string",
@@ -174,10 +174,10 @@ static gchar *internal_sql[] = {
 	"select pg_catalog.current_database(), n.nspname, udt.typname, a.attname, a.attnum, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN NULL ELSE coalesce (nt.nspname || '.', '') || t.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'UDT' || current_database() || '.' || n.nspname || '.' || udt.typname || '.' || a.attnum ELSE NULL END, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)), information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truety
 pmod(a.*, t.*)), NULL, NULL , NULL, NULL, NULL, NULL FROM pg_type udt INNER JOIN pg_namespace n ON (udt.typnamespace=n.oid) INNER JOIN pg_attribute a ON (a.attrelid=udt.typrelid) INNER JOIN pg_type t ON (a.atttypid=t.oid) INNER JOIN pg_namespace nt ON (t.typnamespace = nt.oid) WHERE udt.typrelid != 0 AND (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = udt.typrelid)",
 
 	/* I_STMT_DOMAINS */
-	"SELECT pg_catalog.current_database(), nt.nspname, t.typname, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN NULL ELSE coalesce (nbt.nspname || '.', '') || bt.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'DOM' || current_database() || '.' || nt.nspname || '.' || t.typname ELSE NULL END, 'gchararray', information_schema._pg_char_max_length(t.typbasetype, t.typtypmod), information_schema._pg_char_octet_length(t.typbasetype, t.typtypmod),  NULL, NULL, NULL, NULL, NULL, NULL,  information_schema._pg_numeric_precision(t.typbasetype, t.typtypmod), information_schema._pg_numeric_scale(t.typbasetype, t.typtypmod), t.typdefault, pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (nt.nspname || '.', '') || t.typname END, coalesce (nt.nspname || '.', '') || t.typname, FALSE, o.rolname FROM pg_type t, pg_namespace nt, pg_type bt, pg_namespace nbt, pg_authid o WHERE t.typnamespace = nt.oid AND
  t.typbasetype = bt.oid AND bt.typnamespace = nbt.oid AND t.typtype = 'd' AND o.oid=t.typowner AND pg_catalog.current_database() = ##cat::string AND nt.nspname = ##schema::string", 
+	"SELECT pg_catalog.current_database(), nt.nspname, t.typname, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN NULL ELSE coalesce (nbt.nspname || '.', '') || bt.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'DOM' || current_database() || '.' || nt.nspname || '.' || t.typname ELSE NULL END, 'gchararray', information_schema._pg_char_max_length(t.typbasetype, t.typtypmod), information_schema._pg_char_octet_length(t.typbasetype, t.typtypmod),  NULL, NULL, NULL, NULL, NULL, NULL,  information_schema._pg_numeric_precision(t.typbasetype, t.typtypmod), information_schema._pg_numeric_scale(t.typbasetype, t.typtypmod), t.typdefault, pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (nt.nspname || '.', '') || t.typname END, coalesce (nt.nspname || '.', '') || t.typname, FALSE, o.rolname FROM pg_type t, pg_namespace nt, pg_type bt, pg_namespace nbt, pg_roles o WHERE t.typnamespace = nt.oid AND 
 t.typbasetype = bt.oid AND bt.typnamespace = nbt.oid AND t.typtype = 'd' AND o.oid=t.typowner AND pg_catalog.current_database() = ##cat::string AND nt.nspname = ##schema::string", 
 
 	/* I_STMT_DOMAINS_ALL */
-	"SELECT pg_catalog.current_database(), nt.nspname, t.typname, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN NULL ELSE coalesce (nbt.nspname || '.', '') || bt.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'DOM' || current_database() || '.' || nt.nspname || '.' || t.typname ELSE NULL END, 'gchararray', information_schema._pg_char_max_length(t.typbasetype, t.typtypmod), information_schema._pg_char_octet_length(t.typbasetype, t.typtypmod),  NULL, NULL, NULL, NULL, NULL, NULL,  information_schema._pg_numeric_precision(t.typbasetype, t.typtypmod), information_schema._pg_numeric_scale(t.typbasetype, t.typtypmod), t.typdefault, pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (nt.nspname || '.', '') || t.typname END, coalesce (nt.nspname || '.', '') || t.typname, FALSE, o.rolname FROM pg_type t, pg_namespace nt, pg_type bt, pg_namespace nbt, pg_authid o WHERE t.typnamespace = nt.oid AND
  t.typbasetype = bt.oid AND bt.typnamespace = nbt.oid AND t.typtype = 'd' AND o.oid=t.typowner",
+	"SELECT pg_catalog.current_database(), nt.nspname, t.typname, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN NULL ELSE coalesce (nbt.nspname || '.', '') || bt.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'DOM' || current_database() || '.' || nt.nspname || '.' || t.typname ELSE NULL END, 'gchararray', information_schema._pg_char_max_length(t.typbasetype, t.typtypmod), information_schema._pg_char_octet_length(t.typbasetype, t.typtypmod),  NULL, NULL, NULL, NULL, NULL, NULL,  information_schema._pg_numeric_precision(t.typbasetype, t.typtypmod), information_schema._pg_numeric_scale(t.typbasetype, t.typtypmod), t.typdefault, pg_catalog.obj_description(t.oid), CASE WHEN pg_catalog.pg_type_is_visible(t.oid) IS TRUE THEN t.typname ELSE coalesce (nt.nspname || '.', '') || t.typname END, coalesce (nt.nspname || '.', '') || t.typname, FALSE, o.rolname FROM pg_type t, pg_namespace nt, pg_type bt, pg_namespace nbt, pg_roles o WHERE t.typnamespace = nt.oid AND 
 t.typbasetype = bt.oid AND bt.typnamespace = nbt.oid AND t.typtype = 'd' AND o.oid=t.typowner",
 
 	/* I_STMT_DOMAINS_CONSTRAINTS */
 	"SELECT constraint_catalog, constraint_schema, constraint_name, domain_catalog, domain_schema, domain_name, NULL, CASE WHEN is_deferrable = 'YES' THEN TRUE ELSE FALSE END, CASE WHEN initially_deferred = 'YES' THEN TRUE ELSE FALSE END FROM information_schema.domain_constraints WHERE domain_catalog = ##cat::string AND domain_schema = ##schema::string AND domain_name = ##name::string",
@@ -220,13 +220,13 @@ static gchar *internal_sql[] = {
 	"UNION SELECT 'ROUC' || current_database() || '.' || ss.n_nspname || '.' || ((ss.proname::text || '_'::text) || ss.p_oid::text) || '.' || (ss.x).n, current_database(), ss.n_nspname, ((ss.proname::text || '_'::text) || ss.p_oid::text), 'ROUTINE_COL', CASE WHEN at.typelem <> 0::oid AND at.typlen = -1 THEN 'array_spec' ELSE coalesce (ant.nspname || '.', '') || at.typname END, CASE WHEN at.typelem <> 0::oid AND at.typlen = -1 THEN 'ARR' || at.typelem ELSE NULL END, NULL, NULL FROM pg_type t, pg_namespace nt, ( SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proargnames, p.proargmodes, information_schema._pg_expandarray(COALESCE(p.proallargtypes, p.proargtypes::oid[])) AS x FROM pg_namespace n, pg_proc p WHERE n.oid = p.pronamespace AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text))) ss, pg_type at, pg_namespace ant WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid AND at.oid = t.typelem AND at.typnamespace = ant.oid AND (
 ss.proargmodes[(ss.x).n] = 'o' OR ss.proargmodes[(ss.x).n] = 'b')",
 
 	/* I_STMT_ROUTINES_ALL */
-	"SELECT current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, p.proname::information_schema.sql_identifier, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUC' || current_database() || '.' || n.nspname || '.' || p.proname || '.' || p.oid ELSE coalesce (nt.nspname || '.', '') || t.typname END AS rettype, p.proretset, p.pronargs::int, CASE WHEN l.lanname = 'sql'::name THEN 'SQL'::text ELSE 'EXTERNAL'::text END, CASE WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc ELSE NULL::text END, CASE WHEN l.lanname = 'c'::name THEN p.prosrc ELSE NULL::text END, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::information_sch
 ema.character_data AS parameter_style, CASE WHEN p.provolatile = 'i' THEN TRUE ELSE FALSE END, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, CASE WHEN p.proisstrict THEN TRUE ELSE FALSE END, pg_catalog.obj_description(p.oid), CASE WHEN pg_catalog.pg_function_is_visible(p.oid) IS TRUE THEN p.proname ELSE coalesce (n.nspname || '.', '') || p.proname END, coalesce (n.nspname || '.', '') || p.proname, o.rolname FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt, pg_authid o WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text)) AND o.oid=p.proowner",
+	"SELECT current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, p.proname::information_schema.sql_identifier, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUC' || current_database() || '.' || n.nspname || '.' || p.proname || '.' || p.oid ELSE coalesce (nt.nspname || '.', '') || t.typname END AS rettype, p.proretset, p.pronargs::int, CASE WHEN l.lanname = 'sql'::name THEN 'SQL'::text ELSE 'EXTERNAL'::text END, CASE WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc ELSE NULL::text END, CASE WHEN l.lanname = 'c'::name THEN p.prosrc ELSE NULL::text END, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::information_sch
 ema.character_data AS parameter_style, CASE WHEN p.provolatile = 'i' THEN TRUE ELSE FALSE END, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, CASE WHEN p.proisstrict THEN TRUE ELSE FALSE END, pg_catalog.obj_description(p.oid), CASE WHEN pg_catalog.pg_function_is_visible(p.oid) IS TRUE THEN p.proname ELSE coalesce (n.nspname || '.', '') || p.proname END, coalesce (n.nspname || '.', '') || p.proname, o.rolname FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt, pg_roles o WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text)) AND o.oid=p.proowner",
 
 	/* I_STMT_ROUTINES */
-	"SELECT current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, p.proname::information_schema.sql_identifier, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUC' || current_database() || '.' || n.nspname || '.' || p.proname || '.' || p.oid ELSE coalesce (nt.nspname || '.', '') || t.typname END AS rettype, p.proretset, p.pronargs::int, CASE WHEN l.lanname = 'sql'::name THEN 'SQL'::text ELSE 'EXTERNAL'::text END, CASE WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc ELSE NULL::text END, CASE WHEN l.lanname = 'c'::name THEN p.prosrc ELSE NULL::text END, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::information_sch
 ema.character_data AS parameter_style, CASE WHEN p.provolatile = 'i' THEN TRUE ELSE FALSE END, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, CASE WHEN p.proisstrict THEN TRUE ELSE FALSE END, pg_catalog.obj_description(p.oid), CASE WHEN pg_catalog.pg_function_is_visible(p.oid) IS TRUE THEN p.proname ELSE coalesce (n.nspname || '.', '') || p.proname END, coalesce (n.nspname || '.', '') || p.proname, o.rolname FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt, pg_authid o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND n.nspname::information_schema.sql_identifier = ##schema::string AND n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text)) AND o.oid=p.proowner",
+	"SELECT current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, p.proname::information_schema.sql_identifier, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUC' || current_database() || '.' || n.nspname || '.' || p.proname || '.' || p.oid ELSE coalesce (nt.nspname || '.', '') || t.typname END AS rettype, p.proretset, p.pronargs::int, CASE WHEN l.lanname = 'sql'::name THEN 'SQL'::text ELSE 'EXTERNAL'::text END, CASE WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc ELSE NULL::text END, CASE WHEN l.lanname = 'c'::name THEN p.prosrc ELSE NULL::text END, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::information_sch
 ema.character_data AS parameter_style, CASE WHEN p.provolatile = 'i' THEN TRUE ELSE FALSE END, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, CASE WHEN p.proisstrict THEN TRUE ELSE FALSE END, pg_catalog.obj_description(p.oid), CASE WHEN pg_catalog.pg_function_is_visible(p.oid) IS TRUE THEN p.proname ELSE coalesce (n.nspname || '.', '') || p.proname END, coalesce (n.nspname || '.', '') || p.proname, o.rolname FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt, pg_roles o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND n.nspname::information_schema.sql_identifier = ##schema::string AND n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text)) AND o.oid=p.proowner",
 
 	/* I_STMT_ROUTINES_ONE */
-	"SELECT current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, p.proname::information_schema.sql_identifier, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUC' || current_database() || '.' || n.nspname || '.' || p.proname || '.' || p.oid ELSE coalesce (nt.nspname || '.', '') || t.typname END AS rettype, p.proretset, p.pronargs, CASE WHEN l.lanname = 'sql'::name THEN 'SQL'::text ELSE 'EXTERNAL'::text END, CASE WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc ELSE NULL::text END, CASE WHEN l.lanname = 'c'::name THEN p.prosrc ELSE NULL::text END, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::information_schema.c
 haracter_data AS parameter_style, CASE WHEN p.provolatile = 'i' THEN TRUE ELSE FALSE END, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, CASE WHEN p.proisstrict THEN TRUE ELSE FALSE END, pg_catalog.obj_description(p.oid), CASE WHEN pg_catalog.pg_function_is_visible(p.oid) IS TRUE THEN p.proname ELSE coalesce (n.nspname || '.', '') || p.proname END, coalesce (n.nspname || '.', '') || p.proname, o.rolname FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt, pg_authid o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND n.nspname::information_schema.sql_identifier = ##schema::string AND ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier = ##name::string AND n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text)) AND o.oid=p.
 proowner",
+	"SELECT current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, n.nspname::information_schema.sql_identifier, p.proname::information_schema.sql_identifier, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUC' || current_database() || '.' || n.nspname || '.' || p.proname || '.' || p.oid ELSE coalesce (nt.nspname || '.', '') || t.typname END AS rettype, p.proretset, p.pronargs, CASE WHEN l.lanname = 'sql'::name THEN 'SQL'::text ELSE 'EXTERNAL'::text END, CASE WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc ELSE NULL::text END, CASE WHEN l.lanname = 'c'::name THEN p.prosrc ELSE NULL::text END, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::information_schema.c
 haracter_data AS parameter_style, CASE WHEN p.provolatile = 'i' THEN TRUE ELSE FALSE END, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, CASE WHEN p.proisstrict THEN TRUE ELSE FALSE END, pg_catalog.obj_description(p.oid), CASE WHEN pg_catalog.pg_function_is_visible(p.oid) IS TRUE THEN p.proname ELSE coalesce (n.nspname || '.', '') || p.proname END, coalesce (n.nspname || '.', '') || p.proname, o.rolname FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt, pg_roles o WHERE current_database()::information_schema.sql_identifier = ##cat::string AND n.nspname::information_schema.sql_identifier = ##schema::string AND ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier = ##name::string AND n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.oid, 'EXECUTE'::text)) AND o.oid=p.p
 roowner",
 
 	/* I_STMT_ROUTINE_PAR_ALL */
 	"SELECT current_database(), ss.n_nspname, ((ss.proname::text || '_'::text) || ss.p_oid::text), (ss.x).n, CASE WHEN ss.proargmodes IS NULL THEN 'IN'::text WHEN ss.proargmodes[(ss.x).n] = 'i' THEN 'IN' WHEN ss.proargmodes[(ss.x).n] = 'o' THEN 'OUT' WHEN ss.proargmodes[(ss.x).n] = 'b' THEN 'INOUT' ELSE NULL::text END, NULLIF(ss.proargnames[(ss.x).n], ''), CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'array_spec' ELSE coalesce (nt.nspname || '.', '') || t.typname END, CASE WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ROUP' || current_database() || '.' || ss.n_nspname || '.' || ((ss.proname::text || '_'::text) || ss.p_oid::text) || '.' || (ss.x).n ELSE NULL END FROM pg_type t, pg_namespace nt, ( SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proargnames, p.proargmodes, information_schema._pg_expandarray(COALESCE(p.proallargtypes, p.proargtypes::oid[])) AS x FROM pg_namespace n, pg_proc p WHERE n.oid = p.pronamespace AND (pg_has_role(p.proowner, 'USAGE'::
 text) OR has_function_privilege(p.oid, 'EXECUTE'::text))) ss WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid",



[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]