Re: [GENERAL] 88, CREATE FUNCTION ON TABLE - Mailing list pgsql-general

Hi Clark,

Now that we have the PL/pgSQL procedural language (thanks to Jan Wieck)
you can do almost every thing you desire with PostgreSQL in a easy way.
For example; to emulate COBOL level 88, as you suggest you may create
the DECODE function (see attached file).
The other solution as you suggest is to have a function for every level
88
this is a better choice to have high performance... (see attached file)

-Jose'-

--COB

Clark Evans wrote:
>
> 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)
>
> 000-process
>     Display "processing based on 88"
>     Evaluate
>        When active-client
>           ... do stuff ...
>        When historica-client
>           ... do other stuff ...
>        When current-client
>           ... do even more stuff ...
>
> First, I'm sure this is not valid COBOL, I'm only trying to
> demonstrate what an 88 is.  In logical terms, it is a
> predicate, which takes the current fields as parameters.
>
> To write our example above (in oracle),
>
> create table customer
> ( customer_id      number(10),
>   customer_name    varchar(30),
>   customer_status  char(1),
>   sales_rep        number(10)
> );
>
> And then to select all "current"
> clients we get something like this:
>
> select customer_id, customer_name, sales_rep
> from customer
> where customer_status in ('A','a','P'.'p');
>
> Then we can progress.... right?
>
> Well, this is fine on a small scale, but when you
> have 30+ tables with code schemes like this (as I've
> seen in many-a-companies production database)
> it gets to be a horrendous nightmare.
>
> At my assignment before the last assignment, I was
> dealing with this problem, but in a rather annoying way.
> For every table, I'd define a view.  The view would
> do the decode logic... like:
>
> create view customer_view as
> select client_id, client_name, sales_rep,
>    DECODE(client_status,'A',TRUE,'a',TRUE,FALSE) active_client,
>    DECODE(client_status,'H',TRUE,'h',TRUE,FALSE) historical_client,
>    DECODE(client_status,'I',TRUE,'i',TRUE,FALSE) invalid_client,
>    DECODE(client_status,'P',TRUE,'p',TRUE,FALSE) potential_client,
>    DECODE(client_status,'A',TRUE,'a',TRUE,'P',TRUE,'p',TRUE,FALSE)
> current_client
> from customer;
>
> so that you can do:
>
> select customer_id, customer_name, sales_rep
> from customer
> where current_client IS TRUE
>
> huge improvement from a maintenance standpoint.
> A few problems:
>   * It invalidates the indexes *cry* (_big_ problem)
>   * Having IS TRUE is unsightly and annoying.
>   * Using views doubles the number of relations
>   * etc.
>
> Once you get to 30+ tables, this solution,
> aside from the index problem, is very good, but
> it could be better.
>
> I was thinking......
>
> Why couldn't we define a function (sorry I dont' have PosgreSQL
> syntax down yet... so I'm reverting to Oracle PL/SQL) like so:
>
> CREATE FUNCTION is_active_client
> ON client
> IS
> BEGIN
>   RETURN ( client_staus IN ('A','a') );
> END;
>
> Or... better yet, use the "embedded" syntax like
> constraints...
>
> create table customer
> ( customer_id      number(10),
>   customer_name    varchar(30),
>   customer_status  char(1),
>   sales_rep        number(10),
>   --
>   is_active_client customer_status in ('A','a'),
>   etc.
> );
>
> Ok, the syntax needs help, but I hope the point makes sence,
> Then we can have queries like:
>
> select customer_id, customer_name, sales_rep
> from customer
> where is_active_client;
>
> Now!  That is much much more readable and maintainable.
> Also, the "language" for functions "on" a relation could
> be limited to that which will pass through the query
> optimizer, thus the above would merely be "expanded"
> to the appropriate back end sql... traditional "functions"
> have the problem that they typically disable indexes,
> are always called, etc.  These type of boolean functions
> or predicate, if you may, would only be evaluated when
> if needed...
>
> After writing this, I guess this is similar to adding
> a "method" to the table, although I think that
> what I'm proposing is far more limited in scope.
>
> You could have these things to "existence" checks, etc.
>
> aka
>
> CREATE FUNCTION has_line_items
> ON order
> IS
> BEGIN
>    RETURN exists (select 'x' from line_items where line_items.order_no =
> order.order_no);
> END;
>
> Hmm.  I guess what'm trying to do is allow the programmer
> who defines the tables to define the "chunks of logic" that
> make sense in a where clause for the application programmers.
>
> In this way, shielding application programmers from database
> logic changes and also providing self-documentation for the system.
> It would also allow end-users with a MS Query like tool to
> be much more efficient... instead of exposing 4! = 4*3*2*1 = 24 views
> with
> all the possible permutations of queries that an end user
> might want, you could expose the table and 4 of these "predicates".
> A visual tool could then help the manager build their query.
>
> I have implemented this in a corporate system using very large
> views (over 40 additional columns)...but it quickly became painful
> and unmanageable.  I feel that 40 "predicates" would be a
> completely different story...
>
> Advanced version of this feature would allow the function
> to be valid for more than one table, if every target table
> had the required columns.  In postgreSQL, a clear strategy
> would have to be developed for such a feature to work with
> inherited classes, and a nice polymorphism scheme created.
>
> But even if it does not go that far... the "basic" version
> is more than wonderful.
>
> Anyway... what do you all think?  Sorry for being so wordy.
>
> Best,
>
> Clark--Now that we have the PL/pgSQL procedural language (thanks to Jan Wieck)
--you can do almost every thing you desire with PostgreSQL in a easy way.
--For example; to emulate COBOL level 88, as you suggest you may create
--the DECODE function as in:

--COBOL level 88 using views and decode function------------------------------

drop function decode(text,text,bool);
create function decode(text,text,bool) returns bool as '
DECLARE
    stringa text;
    substringa text;
    code text;
    value text;
    i int2;
    z int2;
BEGIN
    stringa:= $2;
    loop
            i:= position(''='' in stringa);
        if i = 0 then
            return $3;
        end if;
            z:= position('','' in stringa);
        if z = 0 then
            z:= textlen(stringa)+1;
        end if;
        value:= substr(stringa,i + 1,z - i - 1);
        code:= substr(stringa,1,i - 1);
        if $1 = code then
            return value;
        else
            if z > 0 then
                stringa:= substr(stringa,z + 1);
            else
                return $3;
            end if;
        end if;
    end loop;
END;
' language 'plpgsql';


drop table customer, customer_view;
create table customer(
    customer_id    numeric,
    customer_name    varchar(30),
    customer_status    char(1),
    sales_rep    numeric
);
insert into customer values (1,'Pippo','A',20);
insert into customer values (2,'Pluto','H',50);
insert into customer values (3,'Topolino','a',10);
insert into customer values (4,'Paperino','P',30);

create view customer_view as
    select customer_id, customer_name, sales_rep,
        DECODE (customer_status,'A=TRUE,a=TRUE',FALSE) as active_client,
        DECODE (customer_status,'H=TRUE,h=TRUE',FALSE) as historical_client,
        DECODE (customer_status,'I=TRUE,i=TRUE',FALSE) as invalid_client,
        DECODE (customer_status,'P=TRUE,p=TRUE',FALSE) as potential_client,
        DECODE (customer_status,'A=TRUE,a=TRUE,P=TRUE,p=TRUE',FALSE) as current_client
           from customer;

select customer_id, customer_name, active_client, sales_rep
from customer_view
where active_client;

select customer_id, customer_name, active_client, sales_rep
from customer_view
where not active_client;

--The other solution as you suggest is to have a function for every level 88
--this is a better choice to have high performance...

--COBOL level 88 using functions-----------------------------------------------

drop function current_client(text);
create function current_client(text) returns bool as '
DECLARE
    status ALIAS for $1;
BEGIN
    RETURN status IN (''A'',''a'',''P'',''p'');
END;
' language 'plpgsql';

drop function active_client(text);
create function active_client(text) returns bool as '
DECLARE
    status ALIAS for $1;
BEGIN
    RETURN status IN (''A'',''a'');
END;
' language 'plpgsql';

select *  from customer
where active_client(customer_status);

select *  from customer
where not active_client(customer_status);