From: Larry Rosenman <ler@lerctr.org>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: John Proctor <jproctor@prium.net>
Cc: Neil Conway <nconway@klamath.dyndns.org>
Cc: josh@agliodbs.com
Cc: peter_e@gmx.net
Cc: pgman@candle.pha.pa.us
Cc: tgl@sss.pgh.pa.us
Cc: pgsql-patches@postgresql.org
Subject: Re: [SQL] 16 parameter limit
Date: 17 Apr 2002 09:41:29 -0500
Message-ID: <1019054495.612.0.camel@lerlaptop> (raw)
In-Reply-To: <004701c1e61c$3f5df700$0200a8c0@SOL>
References: <web-1371993@davinci.ethosmedia.com>
<200204160446.g3G4kha06293@slxmail01.prium.net>
<20020416120437.3e68fcff.nconway@klamath.dyndns.org>
<200204170619.g3H6JKa29940@slxmail01.prium.net>
<004701c1e61c$3f5df700$0200a8c0@SOL>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ler@lerctr.org, chriskl@familyhealth.com.au, jproctor@prium.net, nconway@klamath.dyndns.org, josh@agliodbs.com, peter_e@gmx.net, pgman@candle.pha.pa.us, tgl@sss.pgh.pa.us, pgsql-patches@postgresql.org
Subject: Re: [SQL] 16 parameter limit
In-Reply-To: <1019054495.612.0.camel@lerlaptop>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox