Re: New SQL Datatype RECURRINGCHAR - Mailing list pgsql-hackers

This would be a potential feature of being able to insert into views
in general.  Reversing the CREATE VIEW statement to accept inserts,
deletes and updates.

If true, focus on that.  Theres lots of views that cannot be reversed
properly -- unions come to mind -- but perhaps this type of simple
join could be a first step in the package.  I believe this is on the
TODO list already.

Different attack, but accomplishes the same thing within SQL standards
as I seem to recall views are supposed to do this where reasonable.


Failing that, implement this type of action the same way as foreign
keys.  Via the described method with automagically created views,
tables, etc.  Though I suggest leaving it in contrib for sometime.
Enum functionality isn't particularly useful to the majority whose
applications tend to pull out the numbers for states when the
application is opened (with the assumption they're generally static).

--
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.

----- Original Message -----
From: "Alex Pilosov" <alex@pilosoft.com>
To: "David Bennett" <dbennett@bensoft.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, July 07, 2001 9:24 PM
Subject: RE: [HACKERS] New SQL Datatype RECURRINGCHAR


> On Sat, 7 Jul 2001, David Bennett wrote:
>
> > -----
> > In a nutshell you are recommending:
> > -----
> >
> >   create table contact_type (
> >     code   int2,
> >     type    char(16),
> >     PRIMARY KEY ( code )
> >   );
> >
> >   create table contact (
> >     number  serial,
> >     name    char(32),
> >     type          int2,
> >     PRIMARY KEY ( number ),
> >     FOREIGN KEY ( type ) REFERENCES contact_type ( code )
> >   );
> >
> >   create view contact_with_readble_type as (
> >     select c.number as number,
> >            c.name as name,
> >            t.type as type
> >     from
> >            contact c,
> >            contact_type t
> >   );
> >
> > * To build a type lookup table:
> >
> >   1) Select type and code from contact_type
> >   2) Build UI object which displays type and returns code
> Just 'select distinct' on a view should do just fine.
>
> > * In order to insert a new record with this model:
> >
> >   1) Look up to see if type exists
> >   2) Insert new type
> >   3) Get type ID
> >   4) Insert contact record
> This can be encapsulated with "ON INSERT" rule on a view.
>
> > * The adhoc query user is now faced with
> >   the task of understanding 3 data tables.
> No, only one view. All the logic is encapsulated there.
>
> >
> > -----
> > With recurringchar you could do this easily as:
> > -----
> >
> >   create table contact (
> >     number  serial,
> >     name    char(32),
> >     type          recurringchar1,
> >     PRIMARY KEY ( number ),
> >   );
> >
> > * To build a type lookup table:
> >
> >   1) Select distinct type from contact (optimized access to
recurringchar
> > dictionary)
> >   2) Build UI object which displays and returns type.
> >
> > * In order to insert a new record with this model:
> >
> >   1) Insert contact record
> >
> > * The adhoc query user has one data table.
> >
> > -----
> >
> > Granted, changing the value of contact_type.type would require
edits
> > to the contact records. It may be possible to add simple syntax to
> > allow editing of a 'recurringchar dictionary' to get around
isolated
> > problem which would only exist in certain applications.
> >
> > Actually, maybe 'dictionary' or 'dictref' would be a better name
for
> > the datatype.
> These things belong in application or middleware (AKA
views/triggers), not
> in database server itself.
>
> There are multiple problems with your implementation, for example,
> transaction handling, assume this situation:
>
> Tran A inserts a new contact with new type "foo", but does not
commit.
> Dictionary assigns value of N to 'foo'.
>
> Tran B inserts a new contact with type foo. What value should be
entered
> in the dictionary? N? A new value?
>
> If a type disappears from database, does its dictionary ID get
reused?
>
> All these questions are not simple questions, and its not up to
database
> to decide it. Your preferred solution belongs in your
triggers/views, not
> in core database.
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>