Re: [SQL] 16 parameter limit - Mailing list pgsql-patches
From | Larry Rosenman |
---|---|
Subject | Re: [SQL] 16 parameter limit |
Date | |
Msg-id | 1019054495.612.0.camel@lerlaptop Whole thread Raw |
In response to | Re: [SQL] 16 parameter limit ("Christopher Kings-Lynne" <chriskl@familyhealth.com.au>) |
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
pgsql-patches by date: