RE: [GENERAL] 88, CREATE FUNCTION ON TABLE

> On several occasions I've heard my father, an old cobol hack, cry that
> none of the current databases nor programming languages have an "88".
> Since he's my dad I often ignored him, but finally, some 5 years later
> I now know what he was talking about. In my last contract
> assignment, I was given the job of training / helping a bunch of
> COBOL programmers to convert their code to PL/SQL.
> Their code was "beautiful"... really. It's unfortunate that they had
> to convert to Oracle PL/SQL, which is very much inferior. Anyway,
> in this e-mail I describe exactly what an COBOL level 88 is, and
> suggest an improvement to PostgreSQL.
>
> When defining a data structure, much like a table description,
> a COBOl programmer describes the record by listing its members
> and providing a data type for each. Here is psuto code:
>
> 01 customer
> 10 customer_id pic9(10)
> 10 customer_name picX(30)
> 10 customer_status picX(01)
> 88 active-client value "A" "a".
> 88 historical-client value "H" "h".
> 88 invalid-client value "I" "i".
> 88 potential-client value "P" "p".
> 88 current-client value "A" "a" "P" "p"
> 10 sales_rep pic9(10)

This might be a useful tool to have in postgres.
The way I've always handled this situation in database layout is to use
what I call a valid table.

create table customer (
customer_id int primary key,
customer_name varchar(30),
customer_status char(1),
sales_rep int);
create table valid_c_status (
status char(1) primary key,
active_client boolean DEFAULT FALSE,
historical_client boolean DEFAULT FALSE,
invalid_client boolean DEFAULT FALSE,
potential_client boolean DEFAULT FALSE,
current_client boolean DEFAULT FALSE);

insert into valid_c_status (status, active_client, current_client)
values ('a', TRUE, TRUE);
insert into valid_c_status (status, active_client, current_client)
values ('A', TRUE, TRUE);
insert into valid_c_status (status, historical_client)
values ('h', TRUE);
.
.
.

You basically get the same functionality with a simple join. I realize
that this increases the number of relations, but it also keeps the
amount of duplicate data to a minimum while allowing the use of standard
sql to solve the problem.

Just my $0.02
-DEJ