Re: [SQL] 16 parameter limit

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
>