From 42f17520f55b891b773d516d5e5c42b50fbec58d Mon Sep 17 00:00:00 2001
From: chriskl <chriskl>
Date: Wed, 14 Jan 2004 02:14:28 +0000
Subject: [PATCH] create functions returning arrays and table types.  revert
 frameborder thing as it makes browser unsizeable.

---
 BUGS                            |  3 +--
 HISTORY                         |  1 +
 classes/database/Postgres.php   | 22 +++++++++++++++-------
 classes/database/Postgres72.php | 32 ++++++++++++++------------------
 classes/database/Postgres73.php | 15 ++++++++++++---
 functions.php                   | 18 +++++++++++++-----
 index.php                       |  4 ++--
 7 files changed, 58 insertions(+), 37 deletions(-)

diff --git a/BUGS b/BUGS
index 95e86d7b..9f0cdb17 100644
--- a/BUGS
+++ b/BUGS
@@ -7,14 +7,13 @@ fix getIndexes() and getConstraints() for < 7.3 to know about index type
   (eg. constraints can only be btree indexes)
 re-enable help system
 all DROP and ALTER commands MUST be fully schema-qualified otherwise you can accidentally drop stuff in pg_catalog :(
-need icons for Casts and Conversions, Languages, Aggs and OpClasses
+need icons and Find for Casts and Conversions, Languages, Aggs and OpClasses
 submit changes to HTML_TreeMenu maintainer
 test < 7.3 Find feature for all new objects
 fix constraint search to get domain and table constraints?
 dump sequences when dumping tables
 fix dumping clustering info
 can't edit SELECT results by oid
-investigate phpPgInfo
 
 Need to fix:
 * Variables and processes views for < 7.3
diff --git a/HISTORY b/HISTORY
index 9b9a4aa7..34a4b388 100644
--- a/HISTORY
+++ b/HISTORY
@@ -13,6 +13,7 @@ Features
 * Integration with the PostgreSQL statistics collector.  See
   table and index performance and usage information.
 * Display user session defaults for PostgreSQL >= 7.3
+* Create functions returning arrays and table types
 
 Bugs
 * Object browser fixed for databases with no schemas
diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php
index 1d129380..e97e72c2 100755
--- a/classes/database/Postgres.php
+++ b/classes/database/Postgres.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres.php,v 1.174 2004/01/07 16:29:40 soranzo Exp $
+ * $Id: Postgres.php,v 1.175 2004/01/14 02:14:28 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -2525,14 +2525,22 @@ class Postgres extends BaseDB {
 	 * @param $all If true, will find all available functions, if false just those in search path
 	 * @return A recordet
 	 */
-	function &getTypes($all = false) {
+	function &getTypes($all = false, $tabletypes = false) {
 		global $conf;
 		
-		if ($all || $conf['show_system'])
+		if ($all || $conf['show_system']) {
 			$where = '';
-		else
+		} else {
 			$where = "AND pt.oid > '{$this->_lastSystemOID}'::oid";
-		
+		}
+		// Never show system table types
+		$where2 = "AND c.oid > '{$this->_lastSystemOID}'::oid";
+
+		if ($tabletypes)
+			$tqry = "'c', 'r', 'v'";
+		else
+			$tqry = "'c'";
+
 		$sql = "SELECT
 				pt.typname AS basename,
 				pt.typname,
@@ -2542,8 +2550,8 @@ class Postgres extends BaseDB {
 				pg_user pu
 			WHERE
 				pt.typowner = pu.usesysid
-				AND typrelid = 0
-				AND typname !~ '^_.*'
+				AND (pt.typrelid = 0 OR (SELECT c.relkind IN ({$tqry}) FROM pg_class c WHERE c.oid = pt.typrelid {$where2}))
+				AND typname !~ '^_'
 				{$where}
 			ORDER BY typname
 		";
diff --git a/classes/database/Postgres72.php b/classes/database/Postgres72.php
index 62d6204d..23cf5beb 100644
--- a/classes/database/Postgres72.php
+++ b/classes/database/Postgres72.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres72.php,v 1.55 2003/12/30 03:09:29 chriskl Exp $
+ * $Id: Postgres72.php,v 1.56 2004/01/14 02:14:28 chriskl Exp $
  */
 
 
@@ -245,13 +245,21 @@ class Postgres72 extends Postgres71 {
 	 * @param $all If true, will find all available functions, if false just those in search path
 	 * @return A recordet
 	 */
-	function &getTypes($all = false) {
+	function &getTypes($all = false, $tabletypes = false) {
 		global $conf;
 		
-		if ($all || $conf['show_system'])
+		if ($all || $conf['show_system']) {
 			$where = '';
-		else
+		} else {
 			$where = "AND pt.oid > '{$this->_lastSystemOID}'::oid";
+		}
+		// Never show system table types
+		$where2 = "AND c.oid > '{$this->_lastSystemOID}'::oid";
+		
+		if ($tabletypes)
+			$tqry = "'c', 'r', 'v'";
+		else
+			$tqry = "'c'";
 		
 		$sql = "SELECT
 				pt.typname AS basename,
@@ -262,20 +270,8 @@ class Postgres72 extends Postgres71 {
 				pg_user pu
 			WHERE
 				pt.typowner = pu.usesysid
-				AND typrelid = 0
-				{$where}
-			UNION
-			SELECT
-				pt.typname AS basename,
-				pt.typname,
-				pu.usename AS typowner
-			FROM
-				pg_type pt,
-				pg_user pu
-			WHERE
-				pt.typowner = pu.usesysid
-				AND typrelid = 0
-				AND typname !~ '^_.*'
+				AND (pt.typrelid = 0 OR (SELECT c.relkind IN ({$tqry}) FROM pg_class c WHERE c.oid = pt.typrelid {$where2}))
+				AND typname !~ '^_'
 				{$where}
 			ORDER BY typname
 		";
diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php
index b51dcdb2..8e4ca007 100644
--- a/classes/database/Postgres73.php
+++ b/classes/database/Postgres73.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres73.php,v 1.86 2004/01/03 19:15:44 soranzo Exp $
+ * $Id: Postgres73.php,v 1.87 2004/01/14 02:14:28 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -772,14 +772,22 @@ class Postgres73 extends Postgres72 {
 	/**
 	 * Returns a list of all types in the database
 	 * @param $all If true, will find all available functions, if false just those in search path
+	 * @param $tabletypes If true, will include table types, false will not.
 	 * @return A recordet
 	 */
-	function &getTypes($all = false) {
+	function &getTypes($all = false, $tabletypes = false) {
 		if ($all)
 			$where = 'pg_catalog.pg_type_is_visible(t.oid)';
 		else
 			$where = "n.nspname = '{$this->_schema}'";
+		// Never show system table types
+		$where2 = "AND c.relnamespace NOT IN (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname LIKE 'pg\\\\_%')";
 
+		if ($tabletypes)
+			$tqry = "'c', 'r', 'v'";
+		else
+			$tqry = "'c'";
+			
 		$sql = "SELECT
 				t.typname AS basename,
 				pg_catalog.format_type(t.oid, NULL) AS typname,
@@ -787,7 +795,8 @@ class Postgres73 extends Postgres72 {
 			FROM (pg_catalog.pg_type t
 				LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace)
 				LEFT JOIN pg_catalog.pg_user pu ON t.typowner = pu.usesysid
-			WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND t.typname !~ '^_'
+			WHERE (t.typrelid = 0 OR (SELECT c.relkind IN ({$tqry}) FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid {$where2}))	 
+			AND t.typname !~ '^_'
 			AND {$where}
 			AND t.typtype != 'd'
 			ORDER BY typname
diff --git a/functions.php b/functions.php
index 771655b7..53b74f2d 100644
--- a/functions.php
+++ b/functions.php
@@ -3,7 +3,7 @@
 	/**
 	 * Manage functions in a database
 	 *
-	 * $Id: functions.php,v 1.26 2003/12/30 03:09:29 chriskl Exp $
+	 * $Id: functions.php,v 1.27 2004/01/14 02:14:28 chriskl Exp $
 	 */
 
 	// Include application functions
@@ -205,12 +205,13 @@
 		if (!isset($_POST['formFunction'])) $_POST['formFunction'] = '';
 		if (!isset($_POST['formArguments'])) $_POST['formArguments'] = '';
 		if (!isset($_POST['formReturns'])) $_POST['formReturns'] = '';
-		if (!isset($_POST['formLanguage'])) $_POST['formLanguage'] = '';
+		if (!isset($_POST['formLanguage'])) $_POST['formLanguage'] = 'sql';
 		if (!isset($_POST['formDefinition'])) $_POST['formDefinition'] = '';
 		if (!isset($_POST['formProperties'])) $_POST['formProperties'] = $data->defaultprops;
 		if (!isset($_POST['formSetOf'])) $_POST['formSetOf'] = '';
+		if (!isset($_POST['formArray'])) $_POST['formArray'] = '';
 		
-		$types = &$data->getTypes(true);
+		$types = &$data->getTypes(true, true);
 		$langs = &$data->getLanguages(true);
 
 		echo "<h2>", $misc->printVal($_REQUEST['database']), ": {$lang['strfunctions']}: {$lang['strcreatefunction']}</h2>\n";
@@ -249,6 +250,12 @@
 			$types->moveNext();
 		}
 		echo "</select>\n";
+		
+		// Output array type selector
+		echo "<select name=\"formArray\">\n";
+		echo "<option value=\"\"", ($_POST['formArray'] == '') ? ' selected="selected"' : '', "></option>\n";
+		echo "<option value=\"[]\"", ($_POST['formArray'] == '[]') ? ' selected="selected"' : '', ">[ ]</option>\n";
+		echo "</select></td>\n";
 
 		echo "<td class=\"data1\"><select name=\"formLanguage\">\n";
 		while (!$langs->EOF) {
@@ -300,9 +307,10 @@
 		// Check that they've given a name and a definition
 		if ($_POST['formFunction'] == '') doCreate($lang['strfunctionneedsname']);
 		elseif ($_POST['formDefinition'] == '') doCreate($lang['strfunctionneedsdef']);
-		else {		 
+		else {
+			// Append array symbol to type if chosen
 			$status = $data->createFunction($_POST['formFunction'], $_POST['formArguments'] , 
-					$_POST['formReturns'] , $_POST['formDefinition'] , $_POST['formLanguage'], 
+					$_POST['formReturns'] . $_POST['formArray'] , $_POST['formDefinition'] , $_POST['formLanguage'], 
 					$_POST['formProperties'], $_POST['formSetOf'] == 'SETOF', false);
 			if ($status == 0)
 				doDefault($lang['strfunctioncreated']);
diff --git a/index.php b/index.php
index 238e80a8..fae80edb 100755
--- a/index.php
+++ b/index.php
@@ -3,7 +3,7 @@
 	/**
 	 * Main access point to the app.
 	 *
-	 * $Id: index.php,v 1.9 2004/01/08 09:24:00 chriskl Exp $
+	 * $Id: index.php,v 1.10 2004/01/14 02:14:28 chriskl Exp $
 	 */
 
 	// Include application functions
@@ -11,7 +11,7 @@
 	
 	$misc->printHeader();
 ?>
-<frameset rows="52,*,12" frameborder="0">
+<frameset rows="52,*,12">
 	<frame src="topbar.php" name="topbar" scrolling="no" noresize="noresize" frameborder="0" />
 	<frameset cols="<?php echo $conf['left_width'] ?>,*">
 	  <frame src="browser.php" name="browser" frameborder="0" />
-- 
2.39.5