Re: PL/pgSQL TODO

OK, found it, attached, from April 17.  It is still sitting in my
"review" email box to be summarized into the TODO list.  In fact, I have
~30 emails from that thread that i have to re-read and summarize.

Tom is right, I would have reviewed it by now if it wasn't for my
vacation.


---------------------------------------------------------------------------

Josh Berkus wrote:
> Bruce,
>
> > I have reached this message and I your TODO item discussion was:
> >
> >
>
http://groups.google.com/groups?hl=en&lr=&threadm=20020517114156.Q20428-100000%40megazone23.bigpanda.com&rnum=18&prev=/groups%3Fq%3DJosh%2BBerkus%2Bgroup:comp.databases.postgresql.*%26start%3D10%26hl%3Den%26lr%3D%26scoring%3Dd%26selm%3D20020517114156.Q20428-100000%2540megazone23.bigpanda.com%26rnum%3D18
>
> Nope.  Wrong discussion.  I'm referring to a 14-item list John Proctor
> and I composed to enhance PL/pgSQL and make it equal to Oracle's
> PL/SQL.   I'll dig through my sent mail and find it.
>
> Not that the statement-deferrable constraints would be a bad thing, at
> all.
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
From pgsql-patches-owner+M3698@postgresql.org Wed Apr 17 09:09:11 2002
Return-path: <pgsql-patches-owner+M3698@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
    by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3HD9AS20404
    for <pgman@candle.pha.pa.us>; Wed, 17 Apr 2002 09:09:10 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
    by postgresql.org (Postfix) with SMTP
    id 655364763CB; Wed, 17 Apr 2002 08:51:04 -0400 (EDT)
Received: from slxmail01.prium.net (unknown [64.2.173.194])
    by postgresql.org (Postfix) with ESMTP id 0EDB7475A59
    for <pgsql-patches@postgresql.org>; Wed, 17 Apr 2002 02:21:47 -0400 (EDT)
Received: from there ([192.168.1.199])
    by slxmail01.prium.net (8.11.0/8.8.7) with SMTP id g3H6JKa29940;
    Wed, 17 Apr 2002 02:19:25 -0400
Message-ID: <200204170619.g3H6JKa29940@slxmail01.prium.net>
Content-Type: text/plain;
  charset="iso-8859-1"
From: John Proctor <jproctor@prium.net>
To: Neil Conway <nconway@klamath.dyndns.org>
Subject: Re: [PATCHES] [SQL] 16 parameter limit
Date: Wed, 17 Apr 2002 01:22:14 -0500
X-Mailer: KMail [version 1.3.2]
cc: josh@agliodbs.com, peter_e@gmx.net, pgman@candle.pha.pa.us,
   tgl@sss.pgh.pa.us, pgsql-patches@postgresql.org
References: <web-1371993@davinci.ethosmedia.com> <200204160446.g3G4kha06293@slxmail01.prium.net>
<20020416120437.3e68fcff.nconway@klamath.dyndns.org>
In-Reply-To: <20020416120437.3e68fcff.nconway@klamath.dyndns.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-patches-owner@postgresql.org
Status: OR



OK, here goes.

1) More than 16 parameters. � This can be parameter configurable if 
necessary, but up to 128 would cover 99.9%.

2) Better exception handling. �The procedure should be able to trap any data 
related exception and decide what to do.   No function should ever abort.  It should raise a trappable exception and
letme decide what to do.
 

3) Allow transactions inside of functions. � Mostly for incremental commits. 
Each transaction shoud be implicitely started after any CrUD statement and 
continue until a commit or rollback.

4) Allow autonomous transactions. �This is related to number 2. �In Oracle, I 
can track every single exception and log it in a central table with details, 
even if I rollback the current transaction or savepoint. � This is a must for 
tracking every single database error in an application at the exact point of 
failure.

5) Find a way to get rid of the requirement to quote the entire proc. � This 
is very clumsy. � The PL/pgSQL interpreter should be able to do the quoting 
and escape what it needs.

6) Allow function parameters to be specified by name and type during the definition. Even aliasing is cumbersome and
errorprone on large procs, especially during development when changes are frequent.
 

7) Allow function parameters to be passed by name, not just positional. �i.e. 
get_employee_salary(emp_id => 12345, tax_year => 2001).

8) Add packages. �This is a great way to group related functions, create 
reusable objects, like cursors, etc.

9) Allow anonymous PL/pgSQL blocks. � It should not be required to create a 
function for every PL/pgSQL block. � Often, I just want to do something quick 
and dirty or write complex blocks that I don't even want saved in the 
database. �I can just keep then in a file and execute when necessary.


For those that have not seen Oracle PL/SQL, here is a complete proc that illustrates the simplicity and power of it.

create or replace
procedure bp_cmd_chn (
   i_um_evt_lvl123_idn  in um_evt_lvl123.um_evt_lvl123_idn%type,
   i_chn_class_group_cd in code_chn_class_group.chn_class_group_cd%type
)    
as

/* setup vars for footprinting exceptions */
v_prc        error_log.prc%type := 'bp_cmd_chn';
v_opr        error_log.opr%type := 'init';
v_obj        error_log.obj%type := 'init';

/* local vars */
v_chn_status_cd          um_vendor_chn.chn_status_cd%type;
v_dist_engine_idn        dist_engine.dist_engine_idn%type;
v_dist_format_type_cd    xrf_vendor_format_io.send_dist_format_type_cd%type;
v_io_type_cd             xrf_vendor_format_io.send_io_type_cd%type;
v_app_user_name          app_default_schema.user_name%type;
v_app_schema_name        app_default_schema.app_schema_name%type;
v_send_process_type_cd   xrf_vendor_format_io.send_process_type_cd%type;

/* parameterized cursor */
cursor cur_vnd_chn(
   ci_um_evt_lvl123_idn  number,
   ci_chn_class_group_cd varchar2
) is
select umvnd.rdx_vendor_idn, 
       umvnd.chn_class_cd
from   um_vendor_chn umvnd,
       xrf_chn_class_group xchng
where  umvnd.chn_class_cd = xchng.chn_class_cd
and    umvnd.um_evt_lvl123_idn = ci_um_evt_lvl123_idn
and    umvnd.chn_status_cd = 'PEND'
and    xchng.chn_class_group_cd = ci_chn_class_group_cd;


begin

   savepoint bp_cmd_chn;

   /* open cursor with parameters into row object v_vnd_chn_rec */
   for v_vnd_chn_rec in cur_vnd_chn(i_um_evt_lvl123_idn,
                                    i_chn_class_group_cd) loop
      /* nice clean select into syntax */
      v_opr := 'select into';
      v_obj := 'xrf_vendor_format_io';
      select send_dist_format_type_cd,
             send_io_type_cd,
             send_process_type_cd
      into   v_dist_format_type_cd,
             v_io_type_cd ,
             v_send_process_type_cd
      from   xrf_vendor_format_io
      where  rdx_vendor_idn = v_vnd_chn_rec.rdx_vendor_idn
      and    chn_class_cd   = v_vnd_chn_rec.chn_class_cd;

      /* call procedure passing parms by name */     
      v_opr := 'call';
      v_obj := 'dist_engine_ins';
      dist_engine_ins(dist_engine_idn     => v_dist_engine_idn,
                      pending_dt          => sysdate,
                      source_idn          => i_um_evt_lvl123_idn,
                      source_type         => 'EVTLVL123',
                      dist_format_type_cd => v_dist_format_type_cd,
                      recipient_type_cd   => 'VND',
                      io_type_cd          => v_io_type_cd);
           
       
   end loop;

/* Trap all exceptions, calling pkg_error.log_error with details.
   This will start an autonymous transaction to log the error
   then rollback the current savepoint and re-raise exception for
   the caller
*/
exception
   when others then
      pkg_error.log_error (get_schema_name,v_pkg, v_prc, v_opr, v_obj, sqlcode, sqlerrm);
      rollback to bp_cmd_chn;
      raise;
end bp_cmd_chn;
/ 




On Tuesday 16 April 2002 12:04 pm, Neil Conway wrote:
> On Mon, 15 Apr 2002 23:49:21 -0500
>
> "John Proctor" <jproctor@prium.net> wrote:
> > However, none of the above is of any value if the performance penalty is
> > large.  And PL/pgSQL needs much more that just the param number
> > increased.
>
> John,
>
> Could you elaborate on what enhancements you'd like to see in PL/pgSQL?
>
> Cheers,
>
> Neil

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly