Resubmitting this patch without the legal disclaimers attached to it.

Rgds,
Arul Shaji


----------  FWD:  ----------

Subject: Re: [pgsql-patches] pg_get_domaindef
Date: Thu, 25 Jan 2007 03:18:30 +1100
From: Arul Shaji <[EMAIL PROTECTED]>
To: Neil Conway <[EMAIL PROTECTED]>
Cc: pgsql-patches@postgresql.org

Please find attached the patch with modifications

Rgds,
Arul Shaji

On Sat, 20 Jan 2007 04:44, FAST PostgreSQL wrote:
> On Fri, 19 Jan 2007 17:02, Neil Conway wrote:
> > On Sat, 2007-01-20 at 02:28 +1100, FAST PostgreSQL wrote:
> > > Attached is a small patch that implements the pg_get_domaindef(oid)
> > > function.
> >
> > A few minor comments:
> >
> > - don't use C++-style comments
>
> OK. Can do.
>
> > - why does this code append a "-" to the output when SPI_processed != 1,
> > rather than erroring out?
>
> get_ruledef() does the same.  As the user gets a '-' in that case when a
> non-existent oid is given,  I just wanted to be consistent. Maybe a wrong
> idea ?
>
> > - you probably want to elog(ERROR) if typeTuple is invalid:
>
> Of course.
>
> > +               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));
> > +               }
> >
> > This logic seems pretty awkward. Perhaps simpler would be a check for
> > typnotnull (and then appending "CONSTRAINT NOT NULL"), and then handling
> > the non-typnotnull branch separately.
>
> Yeah agree.
>
> > -Neil
>
> Rgds,
> Arul Shaji
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

-------------------------------------------------------


*** pgsql/src/include/catalog/pg_proc.h	2007-01-24 13:58:40.291574752 +1100
--- workingpgsql/src/include/catalog/pg_proc.h	2007-01-24 15:12:02.410350976 +1100
***************
*** 2315,2321 ****
  DESCR("deparse an encoded expression");
  DATA(insert OID = 1665 (  pg_get_serial_sequence	PGNSP PGUID 12 1 0 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 1 0 f f t f v 0 2279 "" _null_ _null_ _null_ RI_FKey_check_ins - _null_ ));
--- 2315,2322 ----
  DESCR("deparse an encoded expression");
  DATA(insert OID = 1665 (  pg_get_serial_sequence	PGNSP PGUID 12 1 0 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 1 0 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 1 0 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-24 14:12:48.167677992 +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-24 13:58:28.417379904 +1100
--- workingpgsql/src/backend/utils/adt/ruleutils.c	2007-01-24 15:18:57.944180232 +1100
***************
*** 113,118 ****
--- 113,123 ----
  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
***************
*** 135,140 ****
--- 140,146 ----
  							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,
***************
*** 424,429 ****
--- 430,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;
+ 	
+ 	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))
+ 			elog(ERROR, "cache lookup failed for type %u", basetypeOid);
+ 
+ 		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);
+ 		}
+ 
+ 		if (typnotnull)
+ 		{
+ 			appendStringInfo(&buf, "CONSTRAINT NOT NULL ");
+ 		}
+ 		else if (constraint != NULL)
+ 		{
+ 			appendStringInfo(&buf, "CONSTRAINT ");
+ 		}
+ 
+ 		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 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

Reply via email to