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
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-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 4: Have you searched our list archives?
http://archives.postgresql.org
