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


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: (view raw, whole thread or download thread mbox)
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
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

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


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-2018 The PostgreSQL Global Development Group