Support for PostgreSQL schema

Drupal 6.9 doesn't support using PG schemas features as a namespace for its tables.

I added this feature through #375763: Running site-specific SQL commands on database connect.

By default, all tables are created in the 'public' schema, which is hard-coded in engines/schema_pgsql.inc.

The simplest way to detect the active PG schema is with PG function current_schema(), which is available since PG 7.3 (2002-11-27).

To support older versions, you'll probably need to declare a global $pg_schema variable.

global $pg_schema;

$res = db_query("SELECT version() >= 'PostgreSQL 7.3';" );
if (db_result($res)) {
  $res = db_query("SELECT current_schema();" );
  $pg_schema =  db_result($res);
} else {
  $pg_schema =  'public';
}

But I'm not sure it's worth. The enclosed patch will call directly the current_schema() function in queries it is needed.

--- engines/schema_pgsql.inc.orig	2008-05-08 05:16:58.000000000 +0200
+++ engines/schema_pgsql.inc	2009-02-28 15:29:13.000000000 +0100
@@ -33,7 +33,7 @@ function schema_pgsql_inspect($tbl_name 
   // added to our array in the same order.
   //
   $sql = ('SELECT * FROM information_schema.COLUMNS '.
-	  'WHERE table_catalog=\'%s\' AND table_schema=\'public\'');
+	  'WHERE table_catalog=\'%s\' AND table_schema=current_schema()');
   if (isset($tbl_name)) {
     $sql .= 'AND table_name = \'%s\' ';
   }
@@ -136,7 +136,7 @@ function schema_pgsql_inspect($tbl_name 
   // Make sur we caught all the unsigned columns.  I could not get
   // this to work as a left join on the previous query.
   //
-  $res = db_query('SELECT ccu.*, cc.check_clause FROM information_schema.constraint_column_usage ccu INNER JOIN information_schema.check_constraints cc ON ccu.constraint_name=cc.constraint_name WHERE table_schema=\'public\'');
+  $res = db_query('SELECT ccu.*, cc.check_clause FROM information_schema.constraint_column_usage ccu INNER JOIN information_schema.check_constraints cc ON ccu.constraint_name=cc.constraint_name WHERE table_schema=current_schema()');
   while ($r = db_fetch_array($res)) {
     $r['table_name'] = schema_unprefix_table($r['table_name']);
 
@@ -158,7 +158,7 @@ function schema_pgsql_inspect($tbl_name 
     '   pg_get_indexdef(i.indexrelid) AS inddef '.
     'FROM pg_class c, pg_class c2, pg_index i, pg_namespace n '.
     'WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND '.
-    '      c.relnamespace=n.oid AND n.nspname=\'public\' '.
+    '      c.relnamespace=n.oid AND n.nspname=current_schema() '.
     'ORDER BY c2.relname');
   while ($r = db_fetch_array($res)) {
     $r['tblname'] = schema_unprefix_table($r['tblname']);

• Affects: modules/schema/engines/schema_pgsql.inc