Hi,

Attached is a small patch that implements the pg_get_domaindef(oid) function. 

Somethings I am not sure about the patch and which I can fix based on your 
input are as follows.

- I have used SPI interface.  I saw that in ruleutils some methods make use 
of SPI interface whereas others use internal APIs. I wasn't sure about the 
reasons. If this is wrong, I can attempt a rewrite using internal APIs.

- This function does not output the constraint name. I couldn't find the 
constraint name given by the user as part of the create domain command being 
stored in any system catalogs. The entry in pg_constraints store a system 
assigned name. But this may be because of my limited knowledge of the 
internals.

- documentation. If this patch is good then I can do this later.

There may be other issues, which I will be happy to fix.

Rgds,
Arul Shaji
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 
693 481. It is confidential to the ordinary user of the email address to which 
it was addressed and may contain copyright and/or legally privileged 
information. No one else may read, print, store, copy or forward all or any of 
it or its attachments. If you receive this email in error, please return to 
sender. Thank you.

If you do not wish to receive commercial email messages from Fujitsu Australia 
Software Technology Pty Ltd, please email [EMAIL PROTECTED]
*** pgsql/src/include/catalog/pg_proc.h	2007-01-18 15:27:15.000000000 +1100
--- workingpgsql/src/include/catalog/pg_proc.h	2007-01-18 16:23:14.000000000 +1100
***************
*** 2311,2317 ****
  DESCR("deparse an encoded expression");
  DATA(insert OID = 1665 (  pg_get_serial_sequence	PGNSP PGUID 12 f f t f s 2 25 "25 25" _null_ _null_ _null_	pg_get_serial_sequence - _null_ ));
  DESCR("name of sequence for a serial column");
! 
  
  /* Generic referential integrity constraint triggers */
  DATA(insert OID = 1644 (  RI_FKey_check_ins		PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_ RI_FKey_check_ins - _null_ ));
--- 2311,2318 ----
  DESCR("deparse an encoded expression");
  DATA(insert OID = 1665 (  pg_get_serial_sequence	PGNSP PGUID 12 f f t f s 2 25 "25 25" _null_ _null_ _null_	pg_get_serial_sequence - _null_ ));
  DESCR("name of sequence for a serial column");
! DATA(insert OID = 2950 (  pg_get_domaindef	PGNSP PGUID 12 f f t f s 1 25 "26" _null_ _null_ _null_	pg_get_domaindef - _null_ ));
! DESCR("domain description");
  
  /* Generic referential integrity constraint triggers */
  DATA(insert OID = 1644 (  RI_FKey_check_ins		PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_ RI_FKey_check_ins - _null_ ));
*** pgsql/src/include/utils/builtins.h	2007-01-18 15:27:15.000000000 +1100
--- workingpgsql/src/include/utils/builtins.h	2007-01-18 16:05:01.000000000 +1100
***************
*** 534,539 ****
--- 534,540 ----
  extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
  extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
  extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+ extern Datum pg_get_domaindef(PG_FUNCTION_ARGS);
  extern char *deparse_expression(Node *expr, List *dpcontext,
  				   bool forceprefix, bool showimplicit);
  extern List *deparse_context_for(const char *aliasname, Oid relid);
*** pgsql/src/backend/utils/adt/ruleutils.c	2007-01-16 16:28:58.000000000 +1100
--- workingpgsql/src/backend/utils/adt/ruleutils.c	2007-01-19 14:14:51.776708712 +1100
***************
*** 105,110 ****
--- 105,115 ----
  static void *plan_getviewrule = NULL;
  static char *query_getviewrule = "SELECT * FROM pg_catalog.pg_rewrite WHERE ev_class = $1 AND rulename = $2";
  
+ // Queries to extract domain description
+ static void *plan_getdomainbyOid = NULL;
+ static char *query_getdomainbyOid = "SELECT * FROM pg_catalog.pg_type WHERE oid = $1 AND typtype = 'd'";
+ static void *plan_getconstraintbycontypid = NULL;
+ static char *query_getconstraintbycontypid = "SELECT oid, contype FROM pg_catalog.pg_constraint WHERE contypid = $1";
  
  /* ----------
   * Local functions
***************
*** 127,132 ****
--- 132,138 ----
  							int prettyFlags);
  static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
  				   int prettyFlags);
+ static char *pg_get_domaindef_worker(Oid domainOid, int prettyFlags);
  static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
  			 int prettyFlags);
  static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
***************
*** 416,421 ****
--- 422,625 ----
  	return buf.data;
  }
  
+ /*
+  * get_domaindef - Get the definition of a domain
+  */
+ Datum
+ pg_get_domaindef(PG_FUNCTION_ARGS)
+ {
+ 	Oid	domainOid = PG_GETARG_OID(0);
+ 	PG_RETURN_TEXT_P(string_to_text(pg_get_domaindef_worker(domainOid, 0)));	
+ }
+ 
+ /*
+  * worker code for getting domaindef
+  */
+ static char *
+ pg_get_domaindef_worker(Oid domainOid, int prettyFlags)
+ {
+ 	Datum		args[1];
+ 	char		nulls[1];
+ 	int		spirc;
+ 	HeapTuple	domaintup;
+ 	HeapTuple 	typeTuple;
+ 	TupleDesc	domainttc;
+ 	StringInfoData 	buf;
+ 
+ 	char *domainname;
+ 	char *typebasetype = NULL;
+ 	char *typdefault;
+ 	char *constraint = NULL;
+ 	char * contype = NULL;
+ 	
+ 	int	fno;
+ 	Datum	dat;
+ 	bool	isnull;
+ 	bool 	typnotnull = false;
+ 
+ 	Oid 	basetypeOid;
+ 	Oid	constraintOid;
+ 
+ 	/*
+ 	 * Do this first so that string is alloc'd in outer context not SPI's.
+ 	 */
+ 	initStringInfo(&buf);
+ 
+ 	/*
+ 	 * Connect to SPI manager
+ 	 */
+ 	if (SPI_connect() != SPI_OK_CONNECT)
+ 		elog(ERROR, "SPI_connect failed");
+ 
+ 	/*
+ 	 * On the first call prepare the plan to lookup pg_type. We read
+ 	 * pg_type over the SPI manager instead of using the syscache to be
+ 	 * checked for read access on pg_type.
+ 	 */
+ 	if (plan_getdomainbyOid == NULL)
+ 	{
+ 		Oid	argtypes[1];
+ 		void	*plan;
+ 
+ 		argtypes[0] = OIDOID;
+ 		plan = SPI_prepare(query_getdomainbyOid, 1, argtypes);
+ 		if (plan == NULL)
+ 			elog(ERROR, "SPI_prepare failed for \"%s\"", query_getdomainbyOid);
+ 		plan_getdomainbyOid = SPI_saveplan(plan);
+ 	}
+ 
+ 	/*
+ 	 * Get the pg_type tuple for this domain
+ 	 */
+ 	args[0] = ObjectIdGetDatum(domainOid);
+ 	nulls[0] = ' ';
+ 	spirc = SPI_execute_plan(plan_getdomainbyOid, args, nulls, true, 1);
+ 	if (spirc != SPI_OK_SELECT)
+ 		elog(ERROR, "failed to get pg_type tuple for domain %u", domainOid);
+ 	if (SPI_processed != 1)
+ 		appendStringInfo(&buf, "-");
+ 	else
+ 	{
+ 		/*
+ 		 * Get the types definition and put it into executors memory
+ 		 */
+ 		domaintup = SPI_tuptable->vals[0];
+ 		domainttc = SPI_tuptable->tupdesc;
+ 
+ 		/*
+ 		 * Get the attribute values from the tuple
+ 		 */
+ 		fno = SPI_fnumber(domainttc, "typname");
+ 		dat = SPI_getbinval(domaintup, domainttc, fno, &isnull);
+ 		Assert(!isnull);
+ 		domainname = NameStr(*(DatumGetName(dat)));
+ 
+ 		/*
+ 		 * Start building the domain definition text
+ 		 */
+ 		appendStringInfo(&buf, "CREATE DOMAIN %s AS ",
+ 					quote_identifier(domainname));
+ 
+ 		fno = SPI_fnumber(domainttc, "typbasetype");
+ 		dat = SPI_getbinval(domaintup, domainttc, fno, &isnull);
+ 		Assert(!isnull);
+ 		basetypeOid = DatumGetObjectId(dat);
+ 
+ 		/*
+ 		 * Get the base type of the domain
+ 		 */
+ 		typeTuple = SearchSysCache(TYPEOID,
+ 						ObjectIdGetDatum(basetypeOid),
+ 						0, 0, 0);
+ 
+ 		if (HeapTupleIsValid(typeTuple))
+ 		{
+ 			typebasetype = pstrdup(NameStr(((Form_pg_type) GETSTRUCT(typeTuple))->typname));
+ 			appendStringInfo(&buf, "%s ", quote_identifier(typebasetype));
+ 		}
+ 		ReleaseSysCache(typeTuple);
+ 
+ 		/*
+ 		 * Get the default values if any.
+ 	 	 */
+ 		fno = SPI_fnumber(domainttc, "typdefault");
+ 		typdefault = SPI_getvalue(domaintup, domainttc, fno);
+ 
+ 		if (typdefault != NULL)
+ 		{
+ 			appendStringInfo(&buf, "DEFAULT %s ", quote_identifier(typdefault));	
+ 		}
+ 
+ 		/*
+ 		 * Get the constraints. We won't have the constraint name
+ 		 * if it is just a NOT NULL constraint.
+ 	 	 */
+ 		fno = SPI_fnumber(domainttc, "typnotnull");
+ 		dat = SPI_getbinval(domaintup, domainttc, fno, &isnull);
+ 		Assert(!isnull);
+ 		typnotnull = DatumGetBool(dat);
+ 		
+ 		if (plan_getconstraintbycontypid == NULL)
+ 		{
+ 			Oid	argtypes[1];
+ 			void	*plan;
+ 
+ 			argtypes[0] = OIDOID;
+ 			plan = SPI_prepare(query_getconstraintbycontypid, 1, argtypes);
+ 
+ 			if (plan != NULL)
+ 				plan_getconstraintbycontypid = SPI_saveplan(plan);
+ 		}		
+ 		args[0] = ObjectIdGetDatum(domainOid);
+ 		nulls[0] = ' ';
+ 		SPI_execute_plan(plan_getconstraintbycontypid, args, nulls, true, 1);
+ 
+ 		if (SPI_processed == 1)
+ 		{
+ 			/*
+ 			 * Get the types definition and put it into executors memory
+ 			 */
+ 			domaintup = SPI_tuptable->vals[0];
+ 			domainttc = SPI_tuptable->tupdesc;
+ 
+ 			fno = SPI_fnumber(domainttc, "oid");
+ 			dat = SPI_getbinval(domaintup, domainttc, fno, &isnull);
+ 			Assert(!isnull);
+ 			constraintOid = DatumGetObjectId(dat);
+ 			constraint = pg_get_constraintdef_worker(constraintOid, false, 0);
+ 
+ 			//To see if the constraint is a check constraint
+ 			fno = SPI_fnumber(domainttc, "contype");
+ 			contype = SPI_getvalue(domaintup, domainttc, fno);
+ 		}
+ 
+ 		if (typnotnull || constraint != NULL)
+ 		{
+ 			if ( ( (contype != NULL) && (strcmp(contype, "c") != 0) ) || typnotnull )
+ 			{
+ 				appendStringInfo(&buf, "CONSTRAINT ");
+ 			}
+ 			if (typnotnull)
+ 			{
+ 				appendStringInfo(&buf, "NOT NULL ");
+ 			}
+ 		}
+ 		if (constraint != NULL)
+ 		{
+ 			appendStringInfo(&buf, quote_identifier(constraint));
+ 		}
+ 	}
+ 
+ 	/*
+ 	 * Disconnect from SPI manager
+ 	 */
+ 	if (SPI_finish() != SPI_OK_FINISH)
+ 		elog(ERROR, "SPI_finish failed");
+ 
+ 	return buf.data;
+ }
+ 
+ 
  /* ----------
   * get_triggerdef			- Get the definition of a trigger
   * ----------
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to