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

And can we move the discussion to a more appropriate place (-HACKERS?)?

Thanks.
LER

On Wed, 2002-04-17 at 09:29, Christopher Kings-Lynne wrote:
> 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
> >
>
>
> ---------------------------(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
>
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749