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