Thread: Distinct types
Here is an implementation of distinct types, known from SQL99 and
beyond. They are like domains, except that they don't have defaults or
constraints and they do not allow implicit casting to their base type.
The latter aspect is what makes them distinct types. They are useful to
create more type-safe database schemas, to prevent using generic types
such as text or int for everything and then mixing them in inappropriate
ways. This is something domains are not useful for. Much of the
internals are shared with domains nevertheless. The difference is
really only the casting behavior.
To create a distinct type, just run
CREATE TYPE mystring AS text;
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/commands/typecmds.c ./src/backend/commands/typecmds.c
*** ../cvs-pgsql/src/backend/commands/typecmds.c 2008-10-21 13:52:23.000000000 +0300
--- ./src/backend/commands/typecmds.c 2008-10-31 13:50:50.000000000 +0200
*************** RemoveTypeById(Oid typeOid)
*** 648,654 ****
/*
* DefineDomain
! * Registers a new domain.
*/
void
DefineDomain(CreateDomainStmt *stmt)
--- 648,654 ----
/*
* DefineDomain
! * Registers a new domain or distinct type.
*/
void
DefineDomain(CreateDomainStmt *stmt)
*************** DefineDomain(CreateDomainStmt *stmt)
*** 721,738 ****
basetypeoid = HeapTupleGetOid(typeTup);
/*
! * Base type must be a plain base type, another domain or an enum. Domains
* over pseudotypes would create a security hole. Domains over composite
* types might be made to work in the future, but not today.
*/
typtype = baseType->typtype;
if (typtype != TYPTYPE_BASE &&
typtype != TYPTYPE_DOMAIN &&
typtype != TYPTYPE_ENUM)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("\"%s\" is not a valid base type for a domain",
! TypeNameToString(stmt->typename))));
/* passed by value */
byValue = baseType->typbyval;
--- 721,747 ----
basetypeoid = HeapTupleGetOid(typeTup);
/*
! * Base type must be a plain base type, another domain, distinct type, or an enum. Domains
* over pseudotypes would create a security hole. Domains over composite
* types might be made to work in the future, but not today.
*/
typtype = baseType->typtype;
if (typtype != TYPTYPE_BASE &&
typtype != TYPTYPE_DOMAIN &&
+ typtype != TYPTYPE_DISTINCT &&
typtype != TYPTYPE_ENUM)
! {
! if (stmt->distinct_type)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("\"%s\" is not a valid base type for a distinct type",
! TypeNameToString(stmt->typename))));
! else
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("\"%s\" is not a valid base type for a domain",
! TypeNameToString(stmt->typename))));
! }
/* passed by value */
byValue = baseType->typbyval;
*************** DefineDomain(CreateDomainStmt *stmt)
*** 938,944 ****
InvalidOid, /* relation oid (n/a here) */
0, /* relation kind (ditto) */
internalLength, /* internal size */
! TYPTYPE_DOMAIN, /* type-type (domain type) */
category, /* type-category */
false, /* domain types are never preferred */
delimiter, /* array element delimiter */
--- 947,953 ----
InvalidOid, /* relation oid (n/a here) */
0, /* relation kind (ditto) */
internalLength, /* internal size */
! stmt->distinct_type ? TYPTYPE_DISTINCT : TYPTYPE_DOMAIN, /* type-type (distinct type or
domain)*/
category, /* type-category */
false, /* domain types are never preferred */
delimiter, /* array element delimiter */
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/executor/functions.c ./src/backend/executor/functions.c
*** ../cvs-pgsql/src/backend/executor/functions.c 2008-08-26 01:42:32.000000000 +0300
--- ./src/backend/executor/functions.c 2008-10-30 14:46:05.000000000 +0200
*************** check_sql_fn_retval(Oid func_id, Oid ret
*** 924,929 ****
--- 924,930 ----
fn_typtype = get_typtype(rettype);
if (fn_typtype == TYPTYPE_BASE ||
+ fn_typtype == TYPTYPE_DISTINCT ||
fn_typtype == TYPTYPE_DOMAIN ||
fn_typtype == TYPTYPE_ENUM ||
rettype == VOIDOID)
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/nodes/copyfuncs.c ./src/backend/nodes/copyfuncs.c
*** ../cvs-pgsql/src/backend/nodes/copyfuncs.c 2008-10-22 14:35:44.000000000 +0300
--- ./src/backend/nodes/copyfuncs.c 2008-10-30 14:46:05.000000000 +0200
*************** _copyCreateDomainStmt(CreateDomainStmt *
*** 2657,2662 ****
--- 2657,2663 ----
COPY_NODE_FIELD(domainname);
COPY_NODE_FIELD(typename);
COPY_NODE_FIELD(constraints);
+ COPY_SCALAR_FIELD(distinct_type);
return newnode;
}
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/nodes/equalfuncs.c ./src/backend/nodes/equalfuncs.c
*** ../cvs-pgsql/src/backend/nodes/equalfuncs.c 2008-10-22 14:35:44.000000000 +0300
--- ./src/backend/nodes/equalfuncs.c 2008-10-30 14:46:05.000000000 +0200
*************** _equalCreateDomainStmt(CreateDomainStmt
*** 1342,1347 ****
--- 1342,1348 ----
COMPARE_NODE_FIELD(domainname);
COMPARE_NODE_FIELD(typename);
COMPARE_NODE_FIELD(constraints);
+ COMPARE_SCALAR_FIELD(distinct_type);
return true;
}
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/parser/gram.y ./src/backend/parser/gram.y
*** ../cvs-pgsql/src/backend/parser/gram.y 2008-10-29 13:37:47.000000000 +0200
--- ./src/backend/parser/gram.y 2008-10-30 14:46:05.000000000 +0200
*************** CreateDomainStmt:
*** 5608,5613 ****
--- 5608,5623 ----
n->domainname = $3;
n->typename = $5;
n->constraints = $6;
+ n->distinct_type = FALSE;
+ $$ = (Node *)n;
+ }
+ | CREATE TYPE_P any_name AS Typename
+ {
+ CreateDomainStmt *n = makeNode(CreateDomainStmt);
+ n->domainname = $3;
+ n->typename = $5;
+ n->constraints = NIL;
+ n->distinct_type = TRUE;
$$ = (Node *)n;
}
;
*************** unreserved_keyword:
*** 9439,9445 ****
| ENABLE_P
| ENCODING
| ENCRYPTED
- | ENUM_P
| ESCAPE
| EXCLUDING
| EXCLUSIVE
--- 9449,9454 ----
*************** col_name_keyword:
*** 9626,9631 ****
--- 9635,9641 ----
| COALESCE
| DEC
| DECIMAL_P
+ | ENUM_P
| EXISTS
| EXTRACT
| FLOAT_P
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/parser/keywords.c ./src/backend/parser/keywords.c
*** ../cvs-pgsql/src/backend/parser/keywords.c 2008-10-28 14:31:25.000000000 +0200
--- ./src/backend/parser/keywords.c 2008-10-30 14:46:05.000000000 +0200
*************** const ScanKeyword ScanKeywords[] = {
*** 157,163 ****
{"encoding", ENCODING, UNRESERVED_KEYWORD},
{"encrypted", ENCRYPTED, UNRESERVED_KEYWORD},
{"end", END_P, RESERVED_KEYWORD},
! {"enum", ENUM_P, UNRESERVED_KEYWORD},
{"escape", ESCAPE, UNRESERVED_KEYWORD},
{"except", EXCEPT, RESERVED_KEYWORD},
{"excluding", EXCLUDING, UNRESERVED_KEYWORD},
--- 157,163 ----
{"encoding", ENCODING, UNRESERVED_KEYWORD},
{"encrypted", ENCRYPTED, UNRESERVED_KEYWORD},
{"end", END_P, RESERVED_KEYWORD},
! {"enum", ENUM_P, COL_NAME_KEYWORD},
{"escape", ESCAPE, UNRESERVED_KEYWORD},
{"except", EXCEPT, RESERVED_KEYWORD},
{"excluding", EXCLUDING, UNRESERVED_KEYWORD},
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/parser/parse_coerce.c ./src/backend/parser/parse_coerce.c
*** ../cvs-pgsql/src/backend/parser/parse_coerce.c 2008-10-27 10:14:16.000000000 +0200
--- ./src/backend/parser/parse_coerce.c 2008-10-31 13:52:30.000000000 +0200
*************** find_coercion_pathway(Oid targetTypeId,
*** 1867,1881 ****
*funcid = InvalidOid;
/* Perhaps the types are domains; if so, look at their base types */
! if (OidIsValid(sourceTypeId))
sourceTypeId = getBaseType(sourceTypeId);
! if (OidIsValid(targetTypeId))
targetTypeId = getBaseType(targetTypeId);
/* Domains are always coercible to and from their base type */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
/* Look in pg_cast */
tuple = SearchSysCache(CASTSOURCETARGET,
ObjectIdGetDatum(sourceTypeId),
--- 1867,1891 ----
*funcid = InvalidOid;
/* Perhaps the types are domains; if so, look at their base types */
! if (OidIsValid(sourceTypeId) && get_typtype(sourceTypeId) == TYPTYPE_DOMAIN)
sourceTypeId = getBaseType(sourceTypeId);
! if (OidIsValid(targetTypeId) && get_typtype(targetTypeId) == TYPTYPE_DOMAIN)
targetTypeId = getBaseType(targetTypeId);
/* Domains are always coercible to and from their base type */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
+ /* Distinct types are castable AS ASSIGNMENT to and from their base types */
+ if (ccontext >= COERCION_ASSIGNMENT
+ && ((OidIsValid(sourceTypeId)
+ && get_typtype(sourceTypeId) == TYPTYPE_DISTINCT
+ && getDirectBaseType(sourceTypeId) == targetTypeId)
+ || (OidIsValid(targetTypeId)
+ && get_typtype(targetTypeId) == TYPTYPE_DISTINCT
+ && getDirectBaseType(targetTypeId) == sourceTypeId)))
+ return COERCION_PATH_RELABELTYPE;
+
/* Look in pg_cast */
tuple = SearchSysCache(CASTSOURCETARGET,
ObjectIdGetDatum(sourceTypeId),
*************** find_typmod_coercion_function(Oid typeId
*** 2021,2026 ****
--- 2031,2037 ----
/* Check for a varlena array type (and not a domain) */
if (typeForm->typelem != InvalidOid &&
typeForm->typlen == -1 &&
+ typeForm->typtype != TYPTYPE_DISTINCT &&
typeForm->typtype != TYPTYPE_DOMAIN)
{
/* Yes, switch our attention to the element type */
Only in ./src/backend/parser: parse_coerce.c.~2.170.~
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/utils/adt/format_type.c ./src/backend/utils/adt/format_type.c
*** ../cvs-pgsql/src/backend/utils/adt/format_type.c 2008-03-26 00:42:44.000000000 +0200
--- ./src/backend/utils/adt/format_type.c 2008-10-30 15:30:09.000000000 +0200
*************** format_type_internal(Oid type_oid, int32
*** 147,152 ****
--- 147,153 ----
if (array_base_type != InvalidOid &&
typeform->typstorage != 'p' &&
+ typeform->typtype != TYPTYPE_DISTINCT &&
typeform->typtype != TYPTYPE_DOMAIN)
{
/* Switch our attention to the array element type */
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/utils/adt/xml.c ./src/backend/utils/adt/xml.c
*** ../cvs-pgsql/src/backend/utils/adt/xml.c 2008-10-29 11:32:17.000000000 +0200
--- ./src/backend/utils/adt/xml.c 2008-10-30 14:46:05.000000000 +0200
*************** map_sql_typecoll_to_xmlschema_types(List
*** 2861,2867 ****
}
}
! /* add base types of domains */
foreach(cell0, uniquetypes)
{
Oid typid = lfirst_oid(cell0);
--- 2861,2867 ----
}
}
! /* add base types of distinct types and domains */
foreach(cell0, uniquetypes)
{
Oid typid = lfirst_oid(cell0);
*************** map_sql_type_to_xmlschema_type(Oid typeo
*** 3045,3051 ****
break;
default:
! if (get_typtype(typeoid) == TYPTYPE_DOMAIN)
{
Oid base_typeoid;
int32 base_typmod = -1;
--- 3045,3051 ----
break;
default:
! if (get_typtype(typeoid) == TYPTYPE_DISTINCT || get_typtype(typeoid) == TYPTYPE_DOMAIN)
{
Oid base_typeoid;
int32 base_typmod = -1;
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/utils/cache/lsyscache.c ./src/backend/utils/cache/lsyscache.c
*** ../cvs-pgsql/src/backend/utils/cache/lsyscache.c 2008-10-02 10:50:16.000000000 +0300
--- ./src/backend/utils/cache/lsyscache.c 2008-10-31 12:21:48.000000000 +0200
*************** get_typdefault(Oid typid)
*** 2032,2038 ****
/*
* getBaseType
! * If the given type is a domain, return its base type;
* otherwise return the type's own OID.
*/
Oid
--- 2032,2038 ----
/*
* getBaseType
! * If the given type is a distinct type or domain, return its base type;
* otherwise return the type's own OID.
*/
Oid
*************** getBaseType(Oid typid)
*** 2045,2051 ****
/*
* getBaseTypeAndTypmod
! * If the given type is a domain, return its base type and typmod;
* otherwise return the type's own OID, and leave *typmod unchanged.
*
* Note that the "applied typmod" should be -1 for every domain level
--- 2045,2051 ----
/*
* getBaseTypeAndTypmod
! * If the given type is a distinct type or domain, return its base type and typmod;
* otherwise return the type's own OID, and leave *typmod unchanged.
*
* Note that the "applied typmod" should be -1 for every domain level
*************** getBaseTypeAndTypmod(Oid typid, int32 *t
*** 2069,2077 ****
if (!HeapTupleIsValid(tup))
elog(ERROR, "cache lookup failed for type %u", typid);
typTup = (Form_pg_type) GETSTRUCT(tup);
! if (typTup->typtype != TYPTYPE_DOMAIN)
{
! /* Not a domain, so done */
ReleaseSysCache(tup);
break;
}
--- 2069,2077 ----
if (!HeapTupleIsValid(tup))
elog(ERROR, "cache lookup failed for type %u", typid);
typTup = (Form_pg_type) GETSTRUCT(tup);
! if (typTup->typtype != TYPTYPE_DISTINCT && typTup->typtype != TYPTYPE_DOMAIN)
{
! /* Not a distinct type or domain, so done */
ReleaseSysCache(tup);
break;
}
*************** getBaseTypeAndTypmod(Oid typid, int32 *t
*** 2087,2092 ****
--- 2087,2116 ----
}
/*
+ * getDirectBaseType
+ * If the given type is a distinct type or domain, return its direct base type;
+ * otherwise return the type's own OID.
+ */
+ Oid
+ getDirectBaseType(Oid typid)
+ {
+ HeapTuple tup;
+ Form_pg_type typTup;
+
+ tup = SearchSysCache(TYPEOID,
+ ObjectIdGetDatum(typid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for type %u", typid);
+ typTup = (Form_pg_type) GETSTRUCT(tup);
+ if (typTup->typtype == TYPTYPE_DISTINCT || typTup->typtype == TYPTYPE_DOMAIN)
+ typid = typTup->typbasetype;
+
+ ReleaseSysCache(tup);
+ return typid;
+ }
+
+ /*
* get_typavgwidth
*
* Given a type OID and a typmod value (pass -1 if typmod is unknown),
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/backend/utils/fmgr/funcapi.c ./src/backend/utils/fmgr/funcapi.c
*** ../cvs-pgsql/src/backend/utils/fmgr/funcapi.c 2008-08-26 01:42:34.000000000 +0300
--- ./src/backend/utils/fmgr/funcapi.c 2008-10-30 15:28:47.000000000 +0200
*************** get_type_func_class(Oid typid)
*** 643,648 ****
--- 643,649 ----
case TYPTYPE_COMPOSITE:
return TYPEFUNC_COMPOSITE;
case TYPTYPE_BASE:
+ case TYPTYPE_DISTINCT:
case TYPTYPE_DOMAIN:
case TYPTYPE_ENUM:
return TYPEFUNC_SCALAR;
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/bin/pg_dump/pg_dump.c ./src/bin/pg_dump/pg_dump.c
*** ../cvs-pgsql/src/bin/pg_dump/pg_dump.c 2008-10-02 10:50:16.000000000 +0300
--- ./src/bin/pg_dump/pg_dump.c 2008-10-30 17:01:25.000000000 +0200
*************** static void dumpNamespace(Archive *fout,
*** 133,138 ****
--- 133,139 ----
static void dumpType(Archive *fout, TypeInfo *tinfo);
static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
static void dumpEnumType(Archive *fout, TypeInfo *tinfo);
+ static void dumpDistinctType(Archive *fout, TypeInfo *tinfo);
static void dumpDomain(Archive *fout, TypeInfo *tinfo);
static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
static void dumpShellType(Archive *fout, ShellTypeInfo *stinfo);
*************** dumpType(Archive *fout, TypeInfo *tinfo)
*** 5719,5724 ****
--- 5720,5727 ----
/* Dump out in proper style */
if (tinfo->typtype == TYPTYPE_BASE)
dumpBaseType(fout, tinfo);
+ else if (tinfo->typtype == TYPTYPE_DISTINCT)
+ dumpDistinctType(fout, tinfo);
else if (tinfo->typtype == TYPTYPE_DOMAIN)
dumpDomain(fout, tinfo);
else if (tinfo->typtype == TYPTYPE_COMPOSITE)
*************** dumpBaseType(Archive *fout, TypeInfo *ti
*** 6174,6179 ****
--- 6177,6258 ----
}
/*
+ * dumpDistinctType
+ * writes out to fout the queries to recreate a user-defined distinct type
+ */
+ static void
+ dumpDistinctType(Archive *fout, TypeInfo *tinfo)
+ {
+ PQExpBuffer q = createPQExpBuffer();
+ PQExpBuffer delq = createPQExpBuffer();
+ PQExpBuffer query = createPQExpBuffer();
+ PGresult *res;
+ int ntups;
+ char *typdefn;
+
+ /* Set proper schema search path so type references list correctly */
+ selectSourceSchema(tinfo->dobj.namespace->dobj.name);
+
+ /* Fetch type specific details */
+ appendPQExpBuffer(query, "SELECT pg_catalog.format_type(typbasetype, typtypmod) as typdefn "
+ "FROM pg_catalog.pg_type "
+ "WHERE oid = '%u'::pg_catalog.oid",
+ tinfo->dobj.catId.oid);
+
+ res = PQexec(g_conn, query->data);
+ check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
+
+ /* Expecting a single result only */
+ ntups = PQntuples(res);
+ if (ntups != 1)
+ {
+ write_msg(NULL, "query returned %d rows instead of one: %s\n",
+ ntups, query->data);
+ exit_nicely();
+ }
+
+ typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
+
+ appendPQExpBuffer(q,
+ "CREATE TYPE %s AS %s",
+ fmtId(tinfo->dobj.name),
+ typdefn);
+
+ PQclear(res);
+
+ appendPQExpBuffer(q, ";\n");
+
+ /*
+ * DROP must be fully qualified in case same name appears in pg_catalog
+ */
+ appendPQExpBuffer(delq, "DROP TYPE %s.",
+ fmtId(tinfo->dobj.namespace->dobj.name));
+ appendPQExpBuffer(delq, "%s;\n",
+ fmtId(tinfo->dobj.name));
+
+ ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
+ tinfo->dobj.name,
+ tinfo->dobj.namespace->dobj.name,
+ NULL,
+ tinfo->rolname, false,
+ "TYPE", q->data, delq->data, NULL,
+ tinfo->dobj.dependencies, tinfo->dobj.nDeps,
+ NULL, NULL);
+
+ /* Dump Type Comments */
+ resetPQExpBuffer(q);
+
+ appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
+ dumpComment(fout, q->data,
+ tinfo->dobj.namespace->dobj.name, tinfo->rolname,
+ tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
+
+ destroyPQExpBuffer(q);
+ destroyPQExpBuffer(delq);
+ destroyPQExpBuffer(query);
+ }
+
+ /*
* dumpDomain
* writes out to fout the queries to recreate a user-defined domain
*/
Only in ./src/bin/pg_dump: pg_dump.c.~1.502.~
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/include/catalog/pg_type.h ./src/include/catalog/pg_type.h
*** ../cvs-pgsql/src/include/catalog/pg_type.h 2008-10-15 14:19:43.000000000 +0300
--- ./src/include/catalog/pg_type.h 2008-10-30 14:46:05.000000000 +0200
*************** CATALOG(pg_type,1247) BKI_BOOTSTRAP
*** 60,66 ****
/*
* typtype is 'b' for a base type, 'c' for a composite type (e.g., a
! * table's rowtype), 'd' for a domain type, 'e' for an enum type, or 'p'
* for a pseudo-type. (Use the TYPTYPE macros below.)
*
* If typtype is 'c', typrelid is the OID of the class' entry in pg_class.
--- 60,66 ----
/*
* typtype is 'b' for a base type, 'c' for a composite type (e.g., a
! * table's rowtype), 'd' for a domain type, 'D' for a distinct type, 'e' for an enum type, or 'p'
* for a pseudo-type. (Use the TYPTYPE macros below.)
*
* If typtype is 'c', typrelid is the OID of the class' entry in pg_class.
*************** DATA(insert OID = 3500 ( anyenum PGNSP
*** 631,636 ****
--- 631,637 ----
#define TYPTYPE_BASE 'b' /* base type (ordinary scalar type) */
#define TYPTYPE_COMPOSITE 'c' /* composite (e.g., table's rowtype) */
#define TYPTYPE_DOMAIN 'd' /* domain over another type */
+ #define TYPTYPE_DISTINCT 'D' /* distinct type based on another type */
#define TYPTYPE_ENUM 'e' /* enumerated type */
#define TYPTYPE_PSEUDO 'p' /* pseudo-type */
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/include/nodes/parsenodes.h ./src/include/nodes/parsenodes.h
*** ../cvs-pgsql/src/include/nodes/parsenodes.h 2008-10-15 14:19:43.000000000 +0300
--- ./src/include/nodes/parsenodes.h 2008-10-30 14:46:05.000000000 +0200
*************** typedef struct CreateDomainStmt
*** 1452,1457 ****
--- 1452,1458 ----
List *domainname; /* qualified name (list of Value strings) */
TypeName *typename; /* the base type */
List *constraints; /* constraints (list of Constraint nodes) */
+ bool distinct_type; /* create distinct type rather than domain */
} CreateDomainStmt;
/* ----------------------
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/include/utils/lsyscache.h ./src/include/utils/lsyscache.h
*** ../cvs-pgsql/src/include/utils/lsyscache.h 2008-10-02 10:50:17.000000000 +0300
--- ./src/include/utils/lsyscache.h 2008-10-31 12:21:50.000000000 +0200
*************** extern void getTypeBinaryOutputInfo(Oid
*** 123,128 ****
--- 123,129 ----
extern Oid get_typmodin(Oid typid);
extern Oid getBaseType(Oid typid);
extern Oid getBaseTypeAndTypmod(Oid typid, int32 *typmod);
+ extern Oid getDirectBaseType(Oid typid);
extern int32 get_typavgwidth(Oid typid, int32 typmod);
extern int32 get_attavgwidth(Oid relid, AttrNumber attnum);
extern bool get_attstatsslot(HeapTuple statstuple,
Only in ./src/include/utils: lsyscache.h.~1.126.~
diff -x CVS -x TAGS -prc ../cvs-pgsql/src/pl/plpgsql/src/pl_comp.c ./src/pl/plpgsql/src/pl_comp.c
*** ../cvs-pgsql/src/pl/plpgsql/src/pl_comp.c 2008-10-15 14:19:44.000000000 +0300
--- ./src/pl/plpgsql/src/pl_comp.c 2008-10-30 15:19:33.000000000 +0200
*************** build_datatype(HeapTuple typeTup, int32
*** 1725,1730 ****
--- 1725,1731 ----
switch (typeStruct->typtype)
{
case TYPTYPE_BASE:
+ case TYPTYPE_DISTINCT:
case TYPTYPE_DOMAIN:
case TYPTYPE_ENUM:
typ->ttype = PLPGSQL_TTYPE_SCALAR;
On Fri, 2008-10-31 at 16:03 +0200, Peter Eisentraut wrote:
> Here is an implementation of distinct types, known from SQL99 and
> beyond. They are like domains, except that they don't have defaults or
> constraints and they do not allow implicit casting to their base type.
> The latter aspect is what makes them distinct types. They are useful to
> create more type-safe database schemas, to prevent using generic types
> such as text or int for everything and then mixing them in inappropriate
> ways. This is something domains are not useful for. Much of the
> internals are shared with domains nevertheless. The difference is
> really only the casting behavior.
>
> To create a distinct type, just run
>
> CREATE TYPE mystring AS text;
>
It needs documentation, and I included a quick patch for that (if that's
helpful).
It builds fine for me and appears to do everything as advertised.
I skimmed the code and the relevant parts of the SQL standard, but it
may need further review by someone who knows the type system and the SQL
standard better than I.
Regards,
Jeff Davis
Attachment
On Fri, 2008-11-07 at 09:11 -0800, David E. Wheeler wrote:
> On Nov 6, 2008, at 11:51 PM, Jeff Davis wrote:
>
> > It needs documentation, and I included a quick patch for that (if
> > that's
> > helpful).
>
> You mis-spelled "cast" as "case".
>
Thanks. Updated diff attached.
Regards,
Jeff Davis
Attachment
Peter Eisentraut wrote:
> Here is an implementation of distinct types,
I'm withdrawing this patch from the current commit fest for further
work. For the record, I have attached the current patch, including the
documentation work by Jeff Davis.
Index: doc/src/sgml/ref/create_domain.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v
retrieving revision 1.32
diff -u -3 -p -r1.32 create_domain.sgml
--- doc/src/sgml/ref/create_domain.sgml 14 Nov 2008 10:22:46 -0000 1.32
+++ doc/src/sgml/ref/create_domain.sgml 25 Nov 2008 10:13:18 -0000
@@ -58,6 +58,12 @@ where <replaceable class="PARAMETER">con
Define a domain rather than setting up each table's constraint
individually.
</para>
+
+ <para>
+ Domains are similar to distinct types, described in <xref
+ linkend="sql-createtype">, except that you can specify defaults or
+ constraints, and a domain can be implicitly cast to its base type.
+ </para>
</refsect1>
<refsect1>
Index: doc/src/sgml/ref/create_type.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_type.sgml,v
retrieving revision 1.78
diff -u -3 -p -r1.78 create_type.sgml
--- doc/src/sgml/ref/create_type.sgml 14 Nov 2008 10:22:46 -0000 1.78
+++ doc/src/sgml/ref/create_type.sgml 25 Nov 2008 10:13:18 -0000
@@ -27,6 +27,8 @@ CREATE TYPE <replaceable class="paramete
CREATE TYPE <replaceable class="parameter">name</replaceable> AS ENUM
( '<replaceable class="parameter">label</replaceable>' [, ... ] )
+CREATE TYPE <replaceable class="parameter">name</replaceable> AS <replaceable
class="parameter">data_type</replaceable>
+
CREATE TYPE <replaceable class="parameter">name</replaceable> (
INPUT = <replaceable class="parameter">input_function</replaceable>,
OUTPUT = <replaceable class="parameter">output_function</replaceable>
@@ -96,10 +98,25 @@ CREATE TYPE <replaceable class="paramete
</refsect2>
<refsect2>
+ <title>Distinct Types</title>
+
+ <para>
+ The third form of <command>CREATE TYPE</command> creates a
+ distinct type. Distinct types are similar to domains, as described
+ in <xref linkend="sql-createdomain">, except that they do not have
+ defaults or constraints, and they cannot be implicitly cast to
+ their base type. Distinct types are useful to avoid making
+ mistakes by comparing two unrelated values that happen to be the
+ same type, such as two integers representing supplier number and
+ part number.
+ </para>
+ </refsect2>
+
+ <refsect2>
<title>Base Types</title>
<para>
- The third form of <command>CREATE TYPE</command> creates a new base type
+ The fourth form of <command>CREATE TYPE</command> creates a new base type
(scalar type). To create a new base type, you must be a superuser.
(This restriction is made because an erroneous type definition could
confuse or even crash the server.)
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.126
diff -u -3 -p -r1.126 typecmds.c
--- src/backend/commands/typecmds.c 2 Nov 2008 01:45:28 -0000 1.126
+++ src/backend/commands/typecmds.c 25 Nov 2008 10:13:18 -0000
@@ -648,7 +648,7 @@ RemoveTypeById(Oid typeOid)
/*
* DefineDomain
- * Registers a new domain.
+ * Registers a new domain or distinct type.
*/
void
DefineDomain(CreateDomainStmt *stmt)
@@ -721,18 +721,27 @@ DefineDomain(CreateDomainStmt *stmt)
basetypeoid = HeapTupleGetOid(typeTup);
/*
- * Base type must be a plain base type, another domain or an enum. Domains
+ * Base type must be a plain base type, another domain, distinct type, or an enum. Domains
* over pseudotypes would create a security hole. Domains over composite
* types might be made to work in the future, but not today.
*/
typtype = baseType->typtype;
if (typtype != TYPTYPE_BASE &&
typtype != TYPTYPE_DOMAIN &&
+ typtype != TYPTYPE_DISTINCT &&
typtype != TYPTYPE_ENUM)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("\"%s\" is not a valid base type for a domain",
- TypeNameToString(stmt->typename))));
+ {
+ if (stmt->distinct_type)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("\"%s\" is not a valid base type for a distinct type",
+ TypeNameToString(stmt->typename))));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("\"%s\" is not a valid base type for a domain",
+ TypeNameToString(stmt->typename))));
+ }
/* passed by value */
byValue = baseType->typbyval;
@@ -938,7 +947,7 @@ DefineDomain(CreateDomainStmt *stmt)
InvalidOid, /* relation oid (n/a here) */
0, /* relation kind (ditto) */
internalLength, /* internal size */
- TYPTYPE_DOMAIN, /* type-type (domain type) */
+ stmt->distinct_type ? TYPTYPE_DISTINCT : TYPTYPE_DOMAIN, /* type-type (distinct type or
domain)*/
category, /* type-category */
false, /* domain types are never preferred */
delimiter, /* array element delimiter */
Index: src/backend/executor/functions.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/functions.c,v
retrieving revision 1.128
diff -u -3 -p -r1.128 functions.c
--- src/backend/executor/functions.c 31 Oct 2008 21:07:55 -0000 1.128
+++ src/backend/executor/functions.c 25 Nov 2008 10:13:18 -0000
@@ -1092,6 +1092,7 @@ check_sql_fn_retval(Oid func_id, Oid ret
fn_typtype = get_typtype(rettype);
if (fn_typtype == TYPTYPE_BASE ||
+ fn_typtype == TYPTYPE_DISTINCT ||
fn_typtype == TYPTYPE_DOMAIN ||
fn_typtype == TYPTYPE_ENUM ||
rettype == VOIDOID)
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.413
diff -u -3 -p -r1.413 copyfuncs.c
--- src/backend/nodes/copyfuncs.c 24 Nov 2008 08:46:03 -0000 1.413
+++ src/backend/nodes/copyfuncs.c 25 Nov 2008 10:13:18 -0000
@@ -2659,6 +2659,7 @@ _copyCreateDomainStmt(CreateDomainStmt *
COPY_NODE_FIELD(domainname);
COPY_NODE_FIELD(typename);
COPY_NODE_FIELD(constraints);
+ COPY_SCALAR_FIELD(distinct_type);
return newnode;
}
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.338
diff -u -3 -p -r1.338 equalfuncs.c
--- src/backend/nodes/equalfuncs.c 24 Nov 2008 08:46:03 -0000 1.338
+++ src/backend/nodes/equalfuncs.c 25 Nov 2008 10:13:18 -0000
@@ -1342,6 +1342,7 @@ _equalCreateDomainStmt(CreateDomainStmt
COMPARE_NODE_FIELD(domainname);
COMPARE_NODE_FIELD(typename);
COMPARE_NODE_FIELD(constraints);
+ COMPARE_SCALAR_FIELD(distinct_type);
return true;
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.640
diff -u -3 -p -r2.640 gram.y
--- src/backend/parser/gram.y 24 Nov 2008 08:46:03 -0000 2.640
+++ src/backend/parser/gram.y 25 Nov 2008 10:13:18 -0000
@@ -5623,6 +5623,16 @@ CreateDomainStmt:
n->domainname = $3;
n->typename = $5;
n->constraints = $6;
+ n->distinct_type = FALSE;
+ $$ = (Node *)n;
+ }
+ | CREATE TYPE_P any_name AS Typename
+ {
+ CreateDomainStmt *n = makeNode(CreateDomainStmt);
+ n->domainname = $3;
+ n->typename = $5;
+ n->constraints = NIL;
+ n->distinct_type = TRUE;
$$ = (Node *)n;
}
;
@@ -9477,7 +9487,6 @@ unreserved_keyword:
| ENABLE_P
| ENCODING
| ENCRYPTED
- | ENUM_P
| ESCAPE
| EXCLUDING
| EXCLUSIVE
@@ -9664,6 +9673,7 @@ col_name_keyword:
| COALESCE
| DEC
| DECIMAL_P
+ | ENUM_P
| EXISTS
| EXTRACT
| FLOAT_P
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.205
diff -u -3 -p -r1.205 keywords.c
--- src/backend/parser/keywords.c 27 Oct 2008 09:37:47 -0000 1.205
+++ src/backend/parser/keywords.c 25 Nov 2008 10:13:18 -0000
@@ -157,7 +157,7 @@ const ScanKeyword ScanKeywords[] = {
{"encoding", ENCODING, UNRESERVED_KEYWORD},
{"encrypted", ENCRYPTED, UNRESERVED_KEYWORD},
{"end", END_P, RESERVED_KEYWORD},
- {"enum", ENUM_P, UNRESERVED_KEYWORD},
+ {"enum", ENUM_P, COL_NAME_KEYWORD},
{"escape", ESCAPE, UNRESERVED_KEYWORD},
{"except", EXCEPT, RESERVED_KEYWORD},
{"excluding", EXCLUDING, UNRESERVED_KEYWORD},
Index: src/backend/parser/parse_coerce.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/parse_coerce.c,v
retrieving revision 2.171
diff -u -3 -p -r2.171 parse_coerce.c
--- src/backend/parser/parse_coerce.c 31 Oct 2008 08:39:21 -0000 2.171
+++ src/backend/parser/parse_coerce.c 25 Nov 2008 10:13:18 -0000
@@ -1867,15 +1867,25 @@ find_coercion_pathway(Oid targetTypeId,
*funcid = InvalidOid;
/* Perhaps the types are domains; if so, look at their base types */
- if (OidIsValid(sourceTypeId))
+ if (OidIsValid(sourceTypeId) && get_typtype(sourceTypeId) == TYPTYPE_DOMAIN)
sourceTypeId = getBaseType(sourceTypeId);
- if (OidIsValid(targetTypeId))
+ if (OidIsValid(targetTypeId) && get_typtype(targetTypeId) == TYPTYPE_DOMAIN)
targetTypeId = getBaseType(targetTypeId);
/* Domains are always coercible to and from their base type */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
+ /* Distinct types are castable AS ASSIGNMENT to and from their base types */
+ if (ccontext >= COERCION_ASSIGNMENT
+ && ((OidIsValid(sourceTypeId)
+ && get_typtype(sourceTypeId) == TYPTYPE_DISTINCT
+ && getDirectBaseType(sourceTypeId) == targetTypeId)
+ || (OidIsValid(targetTypeId)
+ && get_typtype(targetTypeId) == TYPTYPE_DISTINCT
+ && getDirectBaseType(targetTypeId) == sourceTypeId)))
+ return COERCION_PATH_RELABELTYPE;
+
/* Look in pg_cast */
tuple = SearchSysCache(CASTSOURCETARGET,
ObjectIdGetDatum(sourceTypeId),
@@ -2033,6 +2043,7 @@ find_typmod_coercion_function(Oid typeId
/* Check for a varlena array type (and not a domain) */
if (typeForm->typelem != InvalidOid &&
typeForm->typlen == -1 &&
+ typeForm->typtype != TYPTYPE_DISTINCT &&
typeForm->typtype != TYPTYPE_DOMAIN)
{
/* Yes, switch our attention to the element type */
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.301
diff -u -3 -p -r1.301 utility.c
--- src/backend/tcop/utility.c 7 Nov 2008 18:25:06 -0000 1.301
+++ src/backend/tcop/utility.c 25 Nov 2008 10:13:18 -0000
@@ -1291,7 +1291,11 @@ CreateCommandTag(Node *parsetree)
break;
case T_CreateDomainStmt:
- tag = "CREATE DOMAIN";
+ {
+ CreateDomainStmt *stmt = (CreateDomainStmt *) parsetree;
+
+ tag = (stmt->distinct_type) ? "CREATE TYPE" : "CREATE DOMAIN";
+ }
break;
case T_CreateSchemaStmt:
Index: src/backend/utils/adt/format_type.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/format_type.c,v
retrieving revision 1.50
diff -u -3 -p -r1.50 format_type.c
--- src/backend/utils/adt/format_type.c 25 Mar 2008 22:42:44 -0000 1.50
+++ src/backend/utils/adt/format_type.c 25 Nov 2008 10:13:18 -0000
@@ -147,6 +147,7 @@ format_type_internal(Oid type_oid, int32
if (array_base_type != InvalidOid &&
typeform->typstorage != 'p' &&
+ typeform->typtype != TYPTYPE_DISTINCT &&
typeform->typtype != TYPTYPE_DOMAIN)
{
/* Switch our attention to the array element type */
Index: src/backend/utils/adt/xml.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.81
diff -u -3 -p -r1.81 xml.c
--- src/backend/utils/adt/xml.c 10 Nov 2008 18:02:20 -0000 1.81
+++ src/backend/utils/adt/xml.c 25 Nov 2008 10:13:18 -0000
@@ -2872,7 +2872,7 @@ map_sql_typecoll_to_xmlschema_types(List
}
}
- /* add base types of domains */
+ /* add base types of distinct types and domains */
foreach(cell0, uniquetypes)
{
Oid typid = lfirst_oid(cell0);
@@ -3056,7 +3056,7 @@ map_sql_type_to_xmlschema_type(Oid typeo
break;
default:
- if (get_typtype(typeoid) == TYPTYPE_DOMAIN)
+ if (get_typtype(typeoid) == TYPTYPE_DISTINCT || get_typtype(typeoid) == TYPTYPE_DOMAIN)
{
Oid base_typeoid;
int32 base_typmod = -1;
Index: src/backend/utils/cache/lsyscache.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/cache/lsyscache.c,v
retrieving revision 1.160
diff -u -3 -p -r1.160 lsyscache.c
--- src/backend/utils/cache/lsyscache.c 28 Sep 2008 19:51:40 -0000 1.160
+++ src/backend/utils/cache/lsyscache.c 25 Nov 2008 10:13:18 -0000
@@ -2032,7 +2032,7 @@ get_typdefault(Oid typid)
/*
* getBaseType
- * If the given type is a domain, return its base type;
+ * If the given type is a distinct type or domain, return its base type;
* otherwise return the type's own OID.
*/
Oid
@@ -2045,7 +2045,7 @@ getBaseType(Oid typid)
/*
* getBaseTypeAndTypmod
- * If the given type is a domain, return its base type and typmod;
+ * If the given type is a distinct type or domain, return its base type and typmod;
* otherwise return the type's own OID, and leave *typmod unchanged.
*
* Note that the "applied typmod" should be -1 for every domain level
@@ -2069,9 +2069,9 @@ getBaseTypeAndTypmod(Oid typid, int32 *t
if (!HeapTupleIsValid(tup))
elog(ERROR, "cache lookup failed for type %u", typid);
typTup = (Form_pg_type) GETSTRUCT(tup);
- if (typTup->typtype != TYPTYPE_DOMAIN)
+ if (typTup->typtype != TYPTYPE_DISTINCT && typTup->typtype != TYPTYPE_DOMAIN)
{
- /* Not a domain, so done */
+ /* Not a distinct type or domain, so done */
ReleaseSysCache(tup);
break;
}
@@ -2087,6 +2087,30 @@ getBaseTypeAndTypmod(Oid typid, int32 *t
}
/*
+ * getDirectBaseType
+ * If the given type is a distinct type or domain, return its direct base type;
+ * otherwise return the type's own OID.
+ */
+Oid
+getDirectBaseType(Oid typid)
+{
+ HeapTuple tup;
+ Form_pg_type typTup;
+
+ tup = SearchSysCache(TYPEOID,
+ ObjectIdGetDatum(typid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for type %u", typid);
+ typTup = (Form_pg_type) GETSTRUCT(tup);
+ if (typTup->typtype == TYPTYPE_DISTINCT || typTup->typtype == TYPTYPE_DOMAIN)
+ typid = typTup->typbasetype;
+
+ ReleaseSysCache(tup);
+ return typid;
+}
+
+/*
* get_typavgwidth
*
* Given a type OID and a typmod value (pass -1 if typmod is unknown),
Index: src/backend/utils/fmgr/funcapi.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/fmgr/funcapi.c,v
retrieving revision 1.42
diff -u -3 -p -r1.42 funcapi.c
--- src/backend/utils/fmgr/funcapi.c 25 Aug 2008 22:42:34 -0000 1.42
+++ src/backend/utils/fmgr/funcapi.c 25 Nov 2008 10:13:18 -0000
@@ -643,6 +643,7 @@ get_type_func_class(Oid typid)
case TYPTYPE_COMPOSITE:
return TYPEFUNC_COMPOSITE;
case TYPTYPE_BASE:
+ case TYPTYPE_DISTINCT:
case TYPTYPE_DOMAIN:
case TYPTYPE_ENUM:
return TYPEFUNC_SCALAR;
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.505
diff -u -3 -p -r1.505 pg_dump.c
--- src/bin/pg_dump/pg_dump.c 9 Nov 2008 21:24:32 -0000 1.505
+++ src/bin/pg_dump/pg_dump.c 25 Nov 2008 10:13:18 -0000
@@ -134,6 +134,7 @@ static void dumpNamespace(Archive *fout,
static void dumpType(Archive *fout, TypeInfo *tinfo);
static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
static void dumpEnumType(Archive *fout, TypeInfo *tinfo);
+static void dumpDistinctType(Archive *fout, TypeInfo *tinfo);
static void dumpDomain(Archive *fout, TypeInfo *tinfo);
static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
static void dumpShellType(Archive *fout, ShellTypeInfo *stinfo);
@@ -5755,6 +5756,8 @@ dumpType(Archive *fout, TypeInfo *tinfo)
/* Dump out in proper style */
if (tinfo->typtype == TYPTYPE_BASE)
dumpBaseType(fout, tinfo);
+ else if (tinfo->typtype == TYPTYPE_DISTINCT)
+ dumpDistinctType(fout, tinfo);
else if (tinfo->typtype == TYPTYPE_DOMAIN)
dumpDomain(fout, tinfo);
else if (tinfo->typtype == TYPTYPE_COMPOSITE)
@@ -6210,6 +6213,82 @@ dumpBaseType(Archive *fout, TypeInfo *ti
}
/*
+ * dumpDistinctType
+ * writes out to fout the queries to recreate a user-defined distinct type
+ */
+static void
+dumpDistinctType(Archive *fout, TypeInfo *tinfo)
+{
+ PQExpBuffer q = createPQExpBuffer();
+ PQExpBuffer delq = createPQExpBuffer();
+ PQExpBuffer query = createPQExpBuffer();
+ PGresult *res;
+ int ntups;
+ char *typdefn;
+
+ /* Set proper schema search path so type references list correctly */
+ selectSourceSchema(tinfo->dobj.namespace->dobj.name);
+
+ /* Fetch type specific details */
+ appendPQExpBuffer(query, "SELECT pg_catalog.format_type(typbasetype, typtypmod) as typdefn "
+ "FROM pg_catalog.pg_type "
+ "WHERE oid = '%u'::pg_catalog.oid",
+ tinfo->dobj.catId.oid);
+
+ res = PQexec(g_conn, query->data);
+ check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
+
+ /* Expecting a single result only */
+ ntups = PQntuples(res);
+ if (ntups != 1)
+ {
+ write_msg(NULL, "query returned %d rows instead of one: %s\n",
+ ntups, query->data);
+ exit_nicely();
+ }
+
+ typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
+
+ appendPQExpBuffer(q,
+ "CREATE TYPE %s AS %s",
+ fmtId(tinfo->dobj.name),
+ typdefn);
+
+ PQclear(res);
+
+ appendPQExpBuffer(q, ";\n");
+
+ /*
+ * DROP must be fully qualified in case same name appears in pg_catalog
+ */
+ appendPQExpBuffer(delq, "DROP TYPE %s.",
+ fmtId(tinfo->dobj.namespace->dobj.name));
+ appendPQExpBuffer(delq, "%s;\n",
+ fmtId(tinfo->dobj.name));
+
+ ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
+ tinfo->dobj.name,
+ tinfo->dobj.namespace->dobj.name,
+ NULL,
+ tinfo->rolname, false,
+ "TYPE", q->data, delq->data, NULL,
+ tinfo->dobj.dependencies, tinfo->dobj.nDeps,
+ NULL, NULL);
+
+ /* Dump Type Comments */
+ resetPQExpBuffer(q);
+
+ appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
+ dumpComment(fout, q->data,
+ tinfo->dobj.namespace->dobj.name, tinfo->rolname,
+ tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
+
+ destroyPQExpBuffer(q);
+ destroyPQExpBuffer(delq);
+ destroyPQExpBuffer(query);
+}
+
+/*
* dumpDomain
* writes out to fout the queries to recreate a user-defined domain
*/
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_type.h,v
retrieving revision 1.203
diff -u -3 -p -r1.203 pg_type.h
--- src/include/catalog/pg_type.h 21 Nov 2008 18:49:24 -0000 1.203
+++ src/include/catalog/pg_type.h 25 Nov 2008 10:13:18 -0000
@@ -60,7 +60,7 @@ CATALOG(pg_type,1247) BKI_BOOTSTRAP
/*
* typtype is 'b' for a base type, 'c' for a composite type (e.g., a
- * table's rowtype), 'd' for a domain type, 'e' for an enum type, or 'p'
+ * table's rowtype), 'd' for a domain type, 'D' for a distinct type, 'e' for an enum type, or 'p'
* for a pseudo-type. (Use the TYPTYPE macros below.)
*
* If typtype is 'c', typrelid is the OID of the class' entry in pg_class.
@@ -632,6 +632,7 @@ DATA(insert OID = 3500 ( anyenum PGNSP
#define TYPTYPE_BASE 'b' /* base type (ordinary scalar type) */
#define TYPTYPE_COMPOSITE 'c' /* composite (e.g., table's rowtype) */
#define TYPTYPE_DOMAIN 'd' /* domain over another type */
+#define TYPTYPE_DISTINCT 'D' /* distinct type based on another type */
#define TYPTYPE_ENUM 'e' /* enumerated type */
#define TYPTYPE_PSEUDO 'p' /* pseudo-type */
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.379
diff -u -3 -p -r1.379 parsenodes.h
--- src/include/nodes/parsenodes.h 24 Nov 2008 08:46:04 -0000 1.379
+++ src/include/nodes/parsenodes.h 25 Nov 2008 10:13:18 -0000
@@ -1461,6 +1461,7 @@ typedef struct CreateDomainStmt
List *domainname; /* qualified name (list of Value strings) */
TypeName *typename; /* the base type */
List *constraints; /* constraints (list of Constraint nodes) */
+ bool distinct_type; /* create distinct type rather than domain */
} CreateDomainStmt;
/* ----------------------
Index: src/include/utils/lsyscache.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/lsyscache.h,v
retrieving revision 1.126
diff -u -3 -p -r1.126 lsyscache.h
--- src/include/utils/lsyscache.h 28 Sep 2008 19:51:40 -0000 1.126
+++ src/include/utils/lsyscache.h 25 Nov 2008 10:13:18 -0000
@@ -123,6 +123,7 @@ extern void getTypeBinaryOutputInfo(Oid
extern Oid get_typmodin(Oid typid);
extern Oid getBaseType(Oid typid);
extern Oid getBaseTypeAndTypmod(Oid typid, int32 *typmod);
+extern Oid getDirectBaseType(Oid typid);
extern int32 get_typavgwidth(Oid typid, int32 typmod);
extern int32 get_attavgwidth(Oid relid, AttrNumber attnum);
extern bool get_attstatsslot(HeapTuple statstuple,
Index: src/interfaces/ecpg/preproc/parse.pl
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/parse.pl,v
retrieving revision 1.1
diff -u -3 -p -r1.1 parse.pl
--- src/interfaces/ecpg/preproc/parse.pl 14 Nov 2008 10:01:04 -0000 1.1
+++ src/interfaces/ecpg/preproc/parse.pl 25 Nov 2008 10:13:18 -0000
@@ -60,6 +60,7 @@ $replace_types{'Sconst'} = 'ignore';
$replace_line{'fetch_direction'} = 'ignore';
$replace_line{"opt_array_boundsopt_array_bounds'['Iconst']'"} = 'ignore';
$replace_line{'col_name_keywordCHAR_P'} = 'ignore';
+$replace_line{'col_name_keywordENUM_P'} = 'ignore';
$replace_line{'col_name_keywordINT_P'} = 'ignore';
$replace_line{'col_name_keywordVALUES'} = 'ignore';
$replace_line{'reserved_keywordTO'} = 'ignore';
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.132
diff -u -3 -p -r1.132 pl_comp.c
--- src/pl/plpgsql/src/pl_comp.c 5 Nov 2008 00:07:53 -0000 1.132
+++ src/pl/plpgsql/src/pl_comp.c 25 Nov 2008 10:13:19 -0000
@@ -1725,6 +1725,7 @@ build_datatype(HeapTuple typeTup, int32
switch (typeStruct->typtype)
{
case TYPTYPE_BASE:
+ case TYPTYPE_DISTINCT:
case TYPTYPE_DOMAIN:
case TYPTYPE_ENUM:
typ->ttype = PLPGSQL_TTYPE_SCALAR;
On Tue, 2008-11-25 at 12:21 +0200, Peter Eisentraut wrote: > Peter Eisentraut wrote: > > Here is an implementation of distinct types, > > I'm withdrawing this patch from the current commit fest for further > work. For the record, I have attached the current patch, including the > documentation work by Jeff Davis. Shame, this was sorely needed. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Simon Riggs wrote: > On Tue, 2008-11-25 at 12:21 +0200, Peter Eisentraut wrote: >> Peter Eisentraut wrote: >>> Here is an implementation of distinct types, >> I'm withdrawing this patch from the current commit fest for further >> work. For the record, I have attached the current patch, including the >> documentation work by Jeff Davis. > > Shame, this was sorely needed. I understand, but the work required to make it work properly is too much under the commit fest spirit right now. In particular, I'm thinking we should try to devise a clever way to make the CREATE ORDERING facility that SQL has for user-defined types interface with our more general operator and operator class mechanisms. This would then also benefit other sorts of user-defined types. There are also a number of unclear assumptions about the domain behavior implicitly in the system that will possibly require a lengthy shaking-out process if we add other sorts of derived types.
On Nov 28, 2008, at 12:46 PM, Peter Eisentraut wrote: > I understand, but the work required to make it work properly is too > much under the commit fest spirit right now. In particular, I'm > thinking we should try to devise a clever way to make the CREATE > ORDERING facility that SQL has for user-defined types interface with > our more general operator and operator class mechanisms. This would > then also benefit other sorts of user-defined types. There are also > a number of unclear assumptions about the domain behavior implicitly > in the system that will possibly require a lengthy shaking-out > process if we add other sorts of derived types Speaking of other sorts of derived types: might they include something just like enums, but sorting on the string values defined for the enum rather than on the order in which the values were defined in the enum? I'd use something like that all the time… Thanks, David
David E. Wheeler wrote: > > Speaking of other sorts of derived types: might they include something > just like enums, but sorting on the string values defined for the enum > rather than on the order in which the values were defined in the enum? > I'd use something like that all the time… > > order by foo_enum::text ... cheers andrew
On Nov 28, 2008, at 5:09 PM, Andrew Dunstan wrote: >> Speaking of other sorts of derived types: might they include >> something just like enums, but sorting on the string values defined >> for the enum rather than on the order in which the values were >> defined in the enum? I'd use something like that all the time… > > order by foo_enum::text ... Ah, I didn't realize that. I guess I'd have to index it on ::text, too. And then, to use the index in WHERE clauses, I'd further have to compare to ::text, eh? Best, David
Peter Eisentraut <peter_e@gmx.net> writes:
> Simon Riggs wrote:
>> Shame, this was sorely needed.
> I understand, but the work required to make it work properly is too much
> under the commit fest spirit right now.
Personally I was wondering exactly why it's "sorely needed". There has
been not one field request for this functionality; in fact I'd never
heard of the feature until Peter popped up with his patch. I assumed he
was simply trying to see if we could tick off another SQL feature
checkbox with a small number of lines of code.
If it's going to take a significant amount of work then I think someone
ought to provide an actual justification why it's worth the work.
regards, tom lane
On Fri, 2008-11-28 at 11:20 -0500, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Simon Riggs wrote: > >> Shame, this was sorely needed. > > > I understand, but the work required to make it work properly is too much > > under the commit fest spirit right now. > > Personally I was wondering exactly why it's "sorely needed". There has > been not one field request for this functionality; in fact I'd never > heard of the feature until Peter popped up with his patch. I assumed he > was simply trying to see if we could tick off another SQL feature > checkbox with a small number of lines of code. > > If it's going to take a significant amount of work then I think someone > ought to provide an actual justification why it's worth the work. Few thoughts: * Domains don't work very well in conjunction with arrays. * Strong typing is preferable in complex applications to avoid errors like sum(ordinal_column). Most developers use this all the time in their 3GL code but cannot use it in SQL. * Allows migration of code easier from places that use strange sounding datatypes that can be mapped easily to existing datatypes. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Fri, 2008-11-28 at 11:20 -0500, Tom Lane wrote:
>> If it's going to take a significant amount of work then I think someone
>> ought to provide an actual justification why it's worth the work.
> Few thoughts:
> * Domains don't work very well in conjunction with arrays.
But distinct types would somehow work better?
> * Strong typing is preferable in complex applications to avoid errors
> like sum(ordinal_column). Most developers use this all the time in their
> 3GL code but cannot use it in SQL.
The problem I see with distinct types is that the typing is *too*
strong --- the datatype has in fact got no usable operations whatever.
> * Allows migration of code easier from places that use strange sounding
> datatypes that can be mapped easily to existing datatypes.
Again, distinct types do *not* provide a "mapping to existing types",
because none of the operations carry along. Domains would be more
nearly what you want for that.
regards, tom lane
On Friday 28 November 2008 18:49:17 Tom Lane wrote: > > * Strong typing is preferable in complex applications to avoid errors > > like sum(ordinal_column). Most developers use this all the time in their > > 3GL code but cannot use it in SQL. > > The problem I see with distinct types is that the typing is *too* > strong --- the datatype has in fact got no usable operations whatever. You are supposed to define your own. It's a new type after all. You only borrow the representation from an existing one. Random example, maybe not the best one: When you create an email type based on text, you don't really want to carry the || operator along, because email || email is not an email (usually). The same applies to substring and pretty much everything else. Domains are not the best solution if you want type safety.
Peter Eisentraut <peter_e@gmx.net> writes:
> On Friday 28 November 2008 18:49:17 Tom Lane wrote:
>> The problem I see with distinct types is that the typing is *too*
>> strong --- the datatype has in fact got no usable operations whatever.
> You are supposed to define your own. It's a new type after all. You only
> borrow the representation from an existing one.
And the I/O functions ... and you still need enough access to the type
to write useful operators for it. Which is not an issue too much at the
C-code level but it sure is at SQL level.
So this seems to me to be a nice conceptual idea but it's still not
clear that it works well in practice.
regards, tom lane
>>> Tom Lane <tgl@sss.pgh.pa.us> wrote: > If it's going to take a significant amount of work then I think someone > ought to provide an actual justification why it's worth the work. This by itself doesn't justify the effort, but I've worked with databases which would refuse to allow comparison (including within JOIN conditions) of values if they were in different domains, and I miss that. It would occasionally keep people from making dumb mistakes that wasted time. It would also be nice to be able to prevent inappropriate use of data, as previously mentioned. I'm afraid I don't have any interest less mundane than that, and ease of use would need to be there in order for it to be useful. I would actually like to see comparisons to literals or expressions of the base type work, although that doesn't seem to be in line with the SQL spec, and I suspect it might not play nice with the implicit casting. -Kevin
I have a feeling what it would actually take to make this useful might be to turn every type into a kind of polymorphic type like our anyelem and anyarray. So substring(mystring) would work and return a mystring but mystring=string would fail. -- greg