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