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(at)prium(dot)net>
> To: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>
> Cc: <josh(at)agliodbs(dot)com>; <peter_e(at)gmx(dot)net>; <pgman(at)candle(dot)pha(dot)pa(dot)us>;
> <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-patches(at)postgresql(dot)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(at)prium(dot)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(at)postgresql(dot)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(at)postgresql(dot)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(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749