Re: [SQL] 16 parameter limit - Mailing list pgsql-patches

I think that this list should definitely be stored in the cvs somewhere -
TODO.detail perhaps, Bruce?

It's good stuff.

Chris

----- Original Message -----
From: "John Proctor" <jproctor@prium.net>
To: "Neil Conway" <nconway@klamath.dyndns.org>
Cc: <josh@agliodbs.com>; <peter_e@gmx.net>; <pgman@candle.pha.pa.us>;
<tgl@sss.pgh.pa.us>; <pgsql-patches@postgresql.org>
Sent: Wednesday, April 17, 2002 2:22 PM
Subject: Re: [PATCHES] [SQL] 16 parameter limit


>
>
> 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 let me 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 error prone 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
>