Skip site navigation (1) Skip section navigation (2)

RE: New SQL Datatype RECURRINGCHAR

From: Alex Pilosov <alex(at)pilosoft(dot)com>
To: David Bennett <dbennett(at)bensoft(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: RE: New SQL Datatype RECURRINGCHAR
Date: 2001-07-07 01:25:09
Message-ID: Pine.BSO.4.10.10107062114050.7004-100000@spider.pilosoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, 6 Jul 2001, David Bennett wrote:

<rest snipped>
> In either model you would:
> 
> 	update master_table set status='OPEN_PENDING_SOMETHING' where status='OPEN'
> 
> This would not change, in fact, even in a normalized design you
> wouldn't change the lookup table (parent) key.  Perhaps you are
> misunderstanding my initial concept.  The MySQL 'enum' is close.  
> However, it is static and requires you to embed business data (your
> key list) in the DDL.  The idea I have here is to dynamically extend
> this list as needed.  I am not saying that the value can't relate to a
> parent (lookup) table.  It's just not necessary if the value is all
> that is needed.
You are making absolutely no sense. 

Let me break it down:


a) To do an update of a key to a different value, you would need to do
following:
1) look up the new value in entire table, find if its already exists
2) If it exists, good.
3) if it doesn't, pick a next number. (out of some sequence, I suppose) to
represent the key.
4) do the actual update.

Step 1 without an index is a killer. Then, you need to have a certain
'table' to map the existing key values to their numerical representations.

How would this 'table' get populated? On startup? On select?

Its one thing to take 'enum' datatype, which I wouldn't disagree too
much with. Its another thing to suggest this kind of a scheme, which
should be really done with views and rules.

I.E. instead of (as you would have) table a(...,  x recurringchar), 
you must have two things:

table a_real (..., x int4)
table lookup (x int4, varchar value)

Then, have a view:
create view a as select ..., value from a_real, lookup where
a_real.x=lookup.x

Then create a rule on insert: (syntax may be rusty)
create rule foo
on insert on table a
do instead
...whatever magic you need to do the actual inserton, lookup, etc.


> --Dave (Hoping some other SQL developers are monitoring this thread :)



In response to

Responses

pgsql-hackers by date

Next:From: Matthew HagertyDate: 2001-07-07 03:09:40
Subject: Async PQgetResult() question.
Previous:From: Larry RosenmanDate: 2001-07-07 00:12:05
Subject: Re: [HACKERS] 2 gig file size limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group