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
