Create Domain....
Started by Rod Tayloralmost 24 years ago2 messages
I've been working away at simple domain support. The simple stuff
works (no constraints, simple data type).
CREATE DOMAIN domainname Typename;
I have a few questions about how to proceed.
So.. Starting with (more) complex datatypes. varchar(), numeric(),
and the like. There is currently no column in pg_type which stores
information similar to atttypmod. So, I'd like to create one ->
pg_type.typmod. The idea is that this value will be copied across to
pg_attribute with other type information if it is not null. Since
pg_type.typeprtlen isn't used (according to docs) would it be safe to
simply rename and resize (int4) this column?
The second part of this is to apply constraints to the domain. That
will require an equivelent to pg_trigger but linked to pg_type, say
pg_domaintrigger. On column creation in a table I'm considering
copying the triggers from pg_domaintrigger to pg_trigger for the
column, and adding a column to pg_trigger which marks them as
originating from the domain. Deletes of a trigger in the domain can
cascade to pg_trigger -- as can updates, etc. Triggers in pg_trigger
with domtrgid NOT NULL would not be (directly) erasable by the ALTER
TABLE DROP CONSTRAINT stuff.
Given the above, ALTER DOMAIN may be complex. ALTER DOMAIN ADD
CONSTRAINT may touch several hundred items -- but I wouldn't expect
this to be a frequent action.
Anyway, patch attached (hopefully it works, I've modified my source to
be in a broken state then). It's against 7.2-HEAD.
With any luck I'm on the right track. Thus far its making a good
weekend project -- but I suspect constraints are going to take alot
longer than that.
Something of great fun however is DROP TYPE text. Lots of neat stuff
happens when you do that. I want to add a RESTRICT & CASCADE
structure to DROP TYPE as well. Cascade may be disabled though.
--
Rod Taylor
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.
Attachments:
domain.patchapplication/octet-stream; name=domain.patchDownload
? GNUmakefile
? config.cache
? config.log
? config.status
? src/GNUmakefile
? src/Makefile.global
? src/backend/postgres.def
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/port/Makefile
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dumpall
? src/bin/scripts/createlang
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/lib/ecpg.def
? src/interfaces/ecpg/lib/ecpg.dll
? src/interfaces/libpgeasy/pgeasy.def
? src/interfaces/libpgeasy/pgeasy.dll
? src/interfaces/libpq/pq.def
? src/interfaces/libpq/pq.dll
? src/pl/plpgsql/src/plpgsql.def
? src/pl/plpgsql/src/plpgsql.dll
Index: src/backend/commands/define.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/define.c,v
retrieving revision 1.64
diff -u -r1.64 define.c
--- src/backend/commands/define.c 2001/10/28 06:25:42 1.64
+++ src/backend/commands/define.c 2002/02/16 22:42:24
@@ -526,6 +526,150 @@
}
/*
+ * DefineDomain
+ * Registers a new domain.
+ */
+void
+DefineDomain(CreateDomainStmt *stmt)
+{
+ int16 internalLength = -1; /* int2 */
+ int16 externalLength = -1; /* int2 */
+ char *inputName = NULL;
+ char *outputName = NULL;
+ char *sendName = NULL;
+ char *receiveName = NULL;
+ char *defaultValue = NULL;
+ bool byValue = false;
+ char delimiter = DEFAULT_TYPDELIM;
+ char alignment = 'i'; /* default alignment */
+ char storage = 'p'; /* default TOAST storage method */
+ char typtype;
+ Datum datum;
+
+ bool isnull;
+ Relation pg_type_rel;
+ TupleDesc pg_type_dsc;
+ HeapTuple typeTup;
+
+ /*
+ * Domainnames, unlike typenames don't need to account for the '_'
+ * prefix. So they can be one character longer.
+ */
+ if (strlen(stmt->domainname) > (NAMEDATALEN - 1))
+ elog(ERROR, "CREATE DOMAIN: domain names must be %d characters or less",
+ NAMEDATALEN - 1);
+
+
+ /* Test for existing Domain (or type) of that name */
+ typeTup = SearchSysCache( TYPENAME
+ , PointerGetDatum(stmt->domainname)
+ , 0, 0, 0
+ );
+ if (HeapTupleIsValid(typeTup))
+ {
+ elog(ERROR, "CREATE DOMAIN: domain or type %s already exists",
+ stmt->domainname);
+ }
+
+
+ /*
+ * Get the information about old types
+ */
+ pg_type_rel = heap_openr(TypeRelationName, RowExclusiveLock);
+ pg_type_dsc = RelationGetDescr(pg_type_rel);
+
+ typeTup = SearchSysCache( TYPENAME
+ , PointerGetDatum(stmt->typename)
+ , 0, 0, 0
+ );
+ if (!HeapTupleIsValid(typeTup))
+ {
+ elog(ERROR, "CREATE DOMAIN: type %s does not exist",
+ stmt->typename);
+ }
+
+ /* Check that this is a basetype */
+ typtype = DatumGetChar(heap_getattr(typeTup, Anum_pg_type_typtype, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+ if (typtype != 'b') {
+ elog(ERROR, "%s is not a basetype", stmt->typename);
+ }
+
+ elog(DEBUG, "NOTE: Extracting information");
+ /* Extract useful data */
+ byValue = DatumGetBool(heap_getattr(typeTup, Anum_pg_type_typbyval, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+ alignment = DatumGetChar(heap_getattr(typeTup, Anum_pg_type_typalign, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+ internalLength = DatumGetInt16(heap_getattr(typeTup, Anum_pg_type_typlen, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+ externalLength = DatumGetInt16(heap_getattr(typeTup, Anum_pg_type_typprtlen, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+ delimiter = DatumGetChar(heap_getattr(typeTup, Anum_pg_type_typdelim, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+
+ datum = heap_getattr(typeTup, Anum_pg_type_typinput, pg_type_dsc, &isnull);
+ Assert(!isnull);
+
+ inputName = DatumGetCString(DirectFunctionCall1(regprocout, datum));
+
+
+ datum = heap_getattr(typeTup, Anum_pg_type_typoutput, pg_type_dsc, &isnull);
+ Assert(!isnull);
+
+ outputName = DatumGetCString(DirectFunctionCall1(regprocout, datum));
+
+
+/* Not used
+ receiveName = DatumGetPointer(heap_getattr(typeTup, Anum_pg_type_typreceive, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+ sendName = DatumGetPointer(heap_getattr(typeTup, Anum_pg_type_typsend, pg_type_dsc, &isnull));
+ Assert(!isnull);
+*/
+
+ storage = DatumGetChar(heap_getattr(typeTup, Anum_pg_type_typstorage, pg_type_dsc, &isnull));
+ Assert(!isnull);
+
+
+ datum = heap_getattr(typeTup, Anum_pg_type_typdefault, pg_type_dsc, &isnull);
+ if (!isnull) {
+ defaultValue = DatumGetCString(DirectFunctionCall1(textout, datum));
+ }
+
+ /*
+ * now have TypeCreate do all the real work.
+ */
+ TypeCreate(stmt->domainname, /* type name */
+ InvalidOid, /* preassigned type oid (not done here) */
+ InvalidOid, /* relation oid (n/a here) */
+ internalLength, /* internal size */
+ externalLength, /* external size */
+ 'd', /* type-type (domain type) */
+ delimiter, /* array element delimiter */
+ inputName, /* input procedure */
+ outputName, /* output procedure */
+ receiveName, /* receive procedure */
+ sendName, /* send procedure */
+ stmt->typename, /* element type name */
+ defaultValue, /* default type value */
+ byValue, /* passed by value */
+ alignment, /* required alignment */
+ storage); /* TOAST strategy */
+
+ /*
+ * Now we can clean up.
+ */
+ heap_close(pg_type_rel, NoLock);
+}
+
+/*
* DefineType
* Registers a new type.
*/
Index: src/backend/commands/remove.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/remove.c,v
retrieving revision 1.65
diff -u -r1.65 remove.c
--- src/backend/commands/remove.c 2001/11/05 17:46:24 1.65
+++ src/backend/commands/remove.c 2002/02/16 22:42:24
@@ -1,7 +1,7 @@
/*-------------------------------------------------------------------------
*
* remove.c
- * POSTGRES remove (function | type | operator ) utilty code.
+ * POSTGRES remove (function | domain | type | operator ) utilty code.
*
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
@@ -22,6 +22,7 @@
#include "commands/comment.h"
#include "commands/defrem.h"
#include "miscadmin.h"
+#include "parser/parse.h"
#include "parser/parse_agg.h"
#include "parser/parse_expr.h"
#include "parser/parse_func.h"
@@ -274,6 +275,62 @@
heap_close(relation, RowExclusiveLock);
}
+
+
+/*
+ * RemoveDomain
+ * Removes the domain 'typeName' and all attributes and relations that
+ * use it.
+ */
+void
+RemoveDomain(char *domainName, int behavior) /* domain name to be removed */
+{
+ Relation relation;
+ HeapTuple tup;
+ TupleDesc description;
+ char typtype;
+ bool isnull;
+
+
+ /* Domains are stored as types. Check for permissions on the type */
+ if (!pg_ownercheck(GetUserId(), domainName, TYPENAME))
+ elog(ERROR, "RemoveDomain: type '%s': permission denied",
+ domainName);
+
+
+ relation = heap_openr(TypeRelationName, RowExclusiveLock);
+ description = RelationGetDescr(relation);
+
+ tup = SearchSysCache(TYPENAME,
+ PointerGetDatum(domainName),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "RemoveType: type '%s' does not exist", domainName);
+
+
+ /* Check that this is actually a domain */
+ typtype = DatumGetChar(heap_getattr(tup, Anum_pg_type_typtype, description, &isnull));
+ Assert(!isnull);
+
+ if (typtype != 'd') {
+ elog(ERROR, "%s is not a domain", domainName);
+ }
+
+ /* CASCADE unsupported */
+ if (behavior == CASCADE) {
+ elog(ERROR, "DROP DOMAIN does not support the CASCADE keyword");
+ }
+
+ /* Delete any comments associated with this type */
+ DeleteComments(tup->t_data->t_oid, RelationGetRelid(relation));
+
+ simple_heap_delete(relation, &tup->t_self);
+
+ ReleaseSysCache(tup);
+
+ heap_close(relation, RowExclusiveLock);
+}
+
/*
* RemoveFunction
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.160
diff -u -r1.160 copyfuncs.c
--- src/backend/nodes/copyfuncs.c 2001/11/05 05:00:14 1.160
+++ src/backend/nodes/copyfuncs.c 2002/02/16 22:42:25
@@ -2210,6 +2210,19 @@
return newnode;
}
+static CreateDomainStmt *
+_copyCreateDomainStmt(CreateDomainStmt *from)
+{
+ CreateDomainStmt *newnode = makeNode(CreateDomainStmt);
+
+ if (from->domainname)
+ newnode->domainname = pstrdup(from->domainname);
+ if (from->typename)
+ newnode->typename = pstrdup(from->typename);
+
+ return newnode;
+}
+
static CreatedbStmt *
_copyCreatedbStmt(CreatedbStmt *from)
{
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.108
diff -u -r1.108 equalfuncs.c
--- src/backend/nodes/equalfuncs.c 2001/11/05 05:00:14 1.108
+++ src/backend/nodes/equalfuncs.c 2002/02/16 22:42:27
@@ -1086,6 +1086,17 @@
}
static bool
+_equalCreateDomainStmt(CreateDomainStmt *a, CreateDomainStmt *b)
+{
+ if (!equalstr(a->domainname, b->domainname))
+ return false;
+ if (!equalstr(a->typename, b->typename))
+ return false;
+
+ return true;
+}
+
+static bool
_equalCreatedbStmt(CreatedbStmt *a, CreatedbStmt *b)
{
if (!equalstr(a->dbname, b->dbname))
@@ -1973,6 +1984,12 @@
case T_LoadStmt:
retval = _equalLoadStmt(a, b);
break;
+ case T_CreateDomainStmt:
+ retval = _equalCreateDomainStmt(a, b);
+ break;
+/* case T_DropDomainStmt:
+ retval = _equalDropdbStmt(a, b);
+ break;*/
case T_CreatedbStmt:
retval = _equalCreatedbStmt(a, b);
break;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.276
diff -u -r2.276 gram.y
--- src/backend/parser/gram.y 2001/12/09 04:39:39 2.276
+++ src/backend/parser/gram.y 2002/02/16 22:42:52
@@ -135,7 +135,8 @@
ClosePortalStmt, ClusterStmt, CommentStmt, ConstraintsSetStmt,
CopyStmt, CreateAsStmt, CreateGroupStmt, CreatePLangStmt,
CreateSchemaStmt, CreateSeqStmt, CreateStmt, CreateTrigStmt,
- CreateUserStmt, CreatedbStmt, CursorStmt, DefineStmt, DeleteStmt,
+ CreateUserStmt, CreateDomainStmt, CreatedbStmt, CursorStmt,
+ DefineStmt, DeleteStmt,
DropGroupStmt, DropPLangStmt, DropSchemaStmt, DropStmt, DropTrigStmt,
DropUserStmt, DropdbStmt, ExplainStmt, FetchStmt,
GrantStmt, IndexStmt, InsertStmt, ListenStmt, LoadStmt, LockStmt,
@@ -284,6 +285,8 @@
%type <list> constraints_set_namelist
%type <boolean> constraints_set_mode
+%type <boolean> opt_as
+
/*
* If you make any token changes, remember to:
* - use "yacc -d" and update parse.h
@@ -338,7 +341,7 @@
WITHOUT
/* Keywords (in SQL92 non-reserved words) */
-%token COMMITTED, SERIALIZABLE, TYPE_P
+%token COMMITTED, SERIALIZABLE, TYPE_P, DOMAIN_P
/* Keywords for Postgres support (not in SQL92 reserved words)
*
@@ -439,6 +442,7 @@
| CopyStmt
| CreateStmt
| CreateAsStmt
+ | CreateDomainStmt
| CreateSchemaStmt
| CreateGroupStmt
| CreateSeqStmt
@@ -748,8 +752,11 @@
n->dbname = $3;
$$ = (Node *)n;
}
+ ;
+
+
/*****************************************************************************
*
* Set PG internal variable
@@ -2039,6 +2046,15 @@
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
n->names = $3;
+ n->behavior = NULL;
+ $$ = (Node *)n;
+ }
+ | DROP DOMAIN_P name_list drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+ n->removeType = DROP_DOMAIN_P;
+ n->names = $3;
+ n->behavior = $4;
$$ = (Node *)n;
}
;
@@ -2071,7 +2087,7 @@
* The COMMENT ON statement can take different forms based upon the type of
* the object associated with the comment. The form of the statement is:
*
- * COMMENT ON [ [ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ]
+ * COMMENT ON [ [ DATABASE | DOMAIN | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ]
* <objname> | AGGREGATE <aggname> (<aggtype>) | FUNCTION
* <funcname> (arg1, arg2, ...) | OPERATOR <op>
* (leftoperand_typ rightoperand_typ) | TRIGGER <triggername> ON
@@ -2157,6 +2173,7 @@
| RULE { $$ = RULE; }
| SEQUENCE { $$ = SEQUENCE; }
| TABLE { $$ = TABLE; }
+ | DOMAIN_P { $$ = TYPE_P; }
| TYPE_P { $$ = TYPE_P; }
| VIEW { $$ = VIEW; }
;
@@ -3129,6 +3146,25 @@
}
;
+/*****************************************************************************
+ *
+ * Manipulate a domain
+ *
+ *
+ *****************************************************************************/
+
+CreateDomainStmt: CREATE DOMAIN_P opt_as name name
+ {
+ CreateDomainStmt *n = makeNode(CreateDomainStmt);
+ n->domainname = $4;
+ n->typename = $5;
+ $$ = (Node *)n;
+ }
+ ;
+
+opt_as: AS {$$ = TRUE; }
+ | /* EMPTY */ {$$ = FALSE; }
+ ;
/*****************************************************************************
*
@@ -5772,6 +5808,7 @@
| DEFERRED { $$ = "deferred"; }
| DELETE { $$ = "delete"; }
| DELIMITERS { $$ = "delimiters"; }
+ | DOMAIN_P { $$ = "domain"; }
| DOUBLE { $$ = "double"; }
| DROP { $$ = "drop"; }
| EACH { $$ = "each"; }
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.99
diff -u -r1.99 keywords.c
--- src/backend/parser/keywords.c 2001/10/10 00:02:42 1.99
+++ src/backend/parser/keywords.c 2002/02/16 22:42:52
@@ -97,6 +97,7 @@
{"desc", DESC},
{"distinct", DISTINCT},
{"do", DO},
+ {"domain", DOMAIN_P},
{"double", DOUBLE},
{"drop", DROP},
{"each", EACH},
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.125
diff -u -r1.125 utility.c
--- src/backend/tcop/utility.c 2002/02/07 00:27:30 1.125
+++ src/backend/tcop/utility.c 2002/02/16 22:42:53
@@ -280,6 +280,11 @@
/* RemoveType does its own permissions checks */
RemoveType(relname);
break;
+
+ case DROP_DOMAIN_P:
+ /* RemoveDomain does its own permissions checks */
+ RemoveDomain(relname, stmt->behavior);
+ break;
}
/*
@@ -796,6 +801,16 @@
set_ps_display(commandTag = "DROP");
DropProceduralLanguage((DropPLangStmt *) parsetree);
+ break;
+
+ /*
+ * ******************************** DOMAIN statements ****
+ *
+ */
+ case T_CreateDomainStmt:
+ set_ps_display(commandTag = "CREATE DOMAIN");
+
+ DefineDomain((CreateDomainStmt *) parsetree);
break;
/*
Index: src/include/commands/defrem.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/defrem.h,v
retrieving revision 1.28
diff -u -r1.28 defrem.h
--- src/include/commands/defrem.h 2001/11/05 17:46:33 1.28
+++ src/include/commands/defrem.h 2002/02/16 22:42:54
@@ -39,10 +39,12 @@
extern void DefineOperator(char *name, List *parameters);
extern void DefineAggregate(char *name, List *parameters);
extern void DefineType(char *name, List *parameters);
+extern void DefineDomain(CreateDomainStmt *stmt);
/*
* prototypes in remove.c
*/
+extern void RemoveDomain(char *domainName, int behavior);
extern void RemoveFunction(char *functionName, List *argTypes);
extern void RemoveOperator(char *operatorName,
char *typeName1, char *typeName2);
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.96
diff -u -r1.96 nodes.h
--- src/include/nodes/nodes.h 2001/11/05 17:46:34 1.96
+++ src/include/nodes/nodes.h 2002/02/16 22:42:54
@@ -172,6 +172,7 @@
T_TransactionStmt,
T_ViewStmt,
T_LoadStmt,
+ T_CreateDomainStmt,
T_CreatedbStmt,
T_DropdbStmt,
T_VacuumStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.151
diff -u -r1.151 parsenodes.h
--- src/include/nodes/parsenodes.h 2001/11/05 17:46:34 1.151
+++ src/include/nodes/parsenodes.h 2002/02/16 22:43:11
@@ -439,12 +439,14 @@
#define DROP_INDEX 4
#define DROP_RULE 5
#define DROP_TYPE_P 6
+#define DROP_DOMAIN_P 7
typedef struct DropStmt
{
NodeTag type;
List *names;
int removeType;
+ int behavior; /* CASCADE or RESTRICT drop behavior */
} DropStmt;
/* ----------------------
@@ -652,6 +654,17 @@
NodeTag type;
char *filename; /* file to load */
} LoadStmt;
+
+/* ----------------------
+ * CreateDomain Statement
+ * ----------------------
+ */
+typedef struct CreateDomainStmt
+{
+ NodeTag type;
+ char *domainname; /* name of domain to create */
+ char *typename; /* name of the type to base it off */
+} CreateDomainStmt;
/* ----------------------
* Createdb Statement
Re: Create Domain....
"Rod Taylor" <rbt@zort.ca> writes:
and the like. There is currently no column in pg_type which stores
information similar to atttypmod. So, I'd like to create one ->
pg_type.typmod. The idea is that this value will be copied across to
pg_attribute with other type information if it is not null. Since
pg_type.typeprtlen isn't used (according to docs) would it be safe to
simply rename and resize (int4) this column?
Make another column. It'll be good for you ;-) ... and you have to
learn how anyway, if you intend to finish out this project.
Something of great fun however is DROP TYPE text.
Yeah, there's not really any support presently for dealing with
dependencies on dropped objects. IMHO it would be a mistake to solve
that just in the context of any one kind of object (such as types);
it's a generic issue.
regards, tom lane