
There was a message posted in March regarding this.   Bruce replied that this 
issue did not come up often.   However, I think there is more to it than 
that.   I think one reason that it does not come up is because most Oracle 
DBAs are not going to dig through mailing lists and take the time to post 
questions.   Once they discover that PL/pgSQL != PL/SQL they just move on.   

I think that the limitations of PL/pgSQL is a huge factor in people not being 
able to use Postgres instead of Oracle.   My company is quite small, but we 
have several very large insurance companies for clients that we develop web 
based applications for.   Currently I have 5 schemas totaling about 1500 
tables and about as many stored procedures and functions.   The applications 
do not even have any permissions on a single table.   All selects are done on 
views and all inserts/updates/deletes are done through stored procedures.   
Our procs have many parameters, one per column or more.   Most of the app 
developers do not even know that much about the schema.   They just know the 
exposed procedural interface.

Other issues similar to this with regards to PL/SQL are the need for packages 
and the ability to declare cursors ahead of time, like in a package so that 
they can be shared and opened when needed.   This also makes much cleaner 
code since the select statement for many cursors clouds the code where it is 
used if it is inline like PL/pgSQL.

Named parameters would also be nice and at least allowing the use of giving 
names to parameters in the declarations instead of $1, $2, etc.

Also, the inablity to trap database "exceptions" is too limiting.   In 
Oracle, we trap every single exception, start an autonomous transacation, log 
the exception to an exception table along with the procedure name, the 
operation being performed and some marker to mke it easy to locate the 
offending statement.  This also allows us to recover, which is very important 
for imports and data loads.

I work with many other Oracle DBAs and I think many have interest in 
Postgres, but also know that without a procedural language on par with PL/SQL 
that it is not possible to switch.   All of  the Oracle shops that I know of 
are very big on PL/SQL and write almost all business logic and table 
interfaces in it.   It also seems that Microsoft SQLServer shops are moving 
in the same direction now that the procedural support for it is getting much 
better.


I am not complaining about Postgres at all.   I think it is fantastic and I 
enjoy using it for personal projects.   However, I think it might be a bit 
misleading to assume that lack of posts regarding the limits of PL/pgSQL 
equate to it being adequate for most large applications.   It is the number 
one reason that I could not use Postgres in 4 large insurance companies.


John Proctor




Tue, 2 Apr 2002 21:50:52 -0500
John Proctor <jproctor@prium.net>
16 parameter limit
John,

You bring up some interesting points.  I agree with you in some parts,
 but some of your difficulties with PL/pgSQL are based on
 misinformation, which would be good to correct.

First, some prefaces:  PL/pgSQL does not currently have a real devoted
 project head.  It was mostly the brainchild of Jan Wieck, who I
 believe developed it as a "side effect" of creating PL/pgTCL.  So one
 of the reasons that the capabilites of PL/pgSQL have been limited is
 that nobody with the required skills has stepped forward from the
 community to take PL/pgSQL to the next stage of development.  The 6
 core developers are a little busy.

Second, with the robustness of Java, J2EE, C++, and Perl::DBI, I
 believe that it has long been assumed by the core developers and a
 majority of the community that any large application would be
 programmed using a seperate middleware langauge and full-blown n-tier
 development.  Thus, for a lot of people, if PL/pgSQL is adequate for
 complex triggers and rules, it is sufficient; if you need incapsulated
 business logic, use Perl or Java.

I'm not putting this forward as what I necessarily believe in, but the
 logic that drives the current "lightweight" nature of PL/pgSQL as
 compared with PL/SQL.  It's an open-source project, though ... hire a
 C programmer and you can change that.

>  I think one reason that it does not come up is because most
>  Oracle 
> DBAs are not going to dig through mailing lists and take the time to
>  post 
> questions.   Once they discover that PL/pgSQL != PL/SQL they just
>  move on.   

Yes, but we're not going to interest those people anyway.  If they
 can't handle using mailing lists as your knowledge base, IMNSHO they
 have no place in the Open Source world.  Stick to expensive,
 well-documented proprietary products.

> I think that the limitations of PL/pgSQL is a huge factor in people
>  not being 
> able to use Postgres instead of Oracle. 

See above.  IMHO, Great Bridge was mistaken to target Oracle instead of
 targeting MS SQL Server as their main competitor, something they paid
 the price for.  I still reccommend Oracle to some (but very few) of my
 customers who need some of the add-ons that come with Oracle and have
 more money than time.

>  The
>  applications 
> do not even have any permissions on a single table.   All selects are
>  done on 
> views and all inserts/updates/deletes are done through stored
>  procedures.   
> Our procs have many parameters, one per column or more.   Most of the
>  app 
> developers do not even know that much about the schema.   They just
>  know the 
> exposed procedural interface.

I've done this on a smaller scale with Postgres + PHP.  It's a good
 rapid development approach for intranet apps, and relatively secure.
  I just don't try to get PL/pgSQL to do anything it can't, and do my
 error handling in interface code.

> Other issues similar to this with regards to PL/SQL are the need for
>  packages 
> and the ability to declare cursors ahead of time, like in a package
>  so that 
> they can be shared and opened when needed.   This also makes much
>  cleaner 
> code since the select statement for many cursors clouds the code
>  where it is 
> used if it is inline like PL/pgSQL.

If you feel strongly enough about this, I am sure that Jan would
 happily give you all of his PL/pgSQL development notes so that you can
 expand the language.

> Named parameters would also be nice and at least allowing the use of
>  giving 
> names to parameters in the declarations instead of $1, $2, etc.

PL/pgSQL has had parameter aliases since Postgres 7.0.0.  

> Also, the inablity to trap database "exceptions" is too limiting.
>    In 
> Oracle, we trap every single exception, start an autonomous
>  transacation, log 
> the exception to an exception table along with the procedure name,
>  the 
> operation being performed and some marker to mke it easy to locate
>  the 
> offending statement.  This also allows us to recover, which is very
>  important 
> for imports and data loads.

This is a singnificant failing.  Once again, I can only point out the
 Postgres team's shortage of skilled manpower.  Wanna donate a
 programmer?  I'd love to see cursor and error handling in PL/pgSQL
 improved, and I can't think that anybody would object.

> It also seems that Microsoft SQLServer shops are
>  moving 
> in the same direction now that the procedural support for it is
>  getting much 
> better.

Here, I disagree.  I am a certified MS SQL Server admin, and PL/pgSQL
 is already miles ahead of Transact-SQL.  Further, Microsoft is not
 improving the procedural elements of T-SQL in new versions because MS
 wants you to use .NET objects and not stored procedures that might be
 portable to another platform.  Perhaps more importantly, MS did not
 write T-SQL (Sybase did), and as a result has trouble modifying it.

> I am not complaining about Postgres at all.   I think it is fantastic
>  and I 
> enjoy using it for personal projects.   However, I think it might be
>  a bit 
> misleading to assume that lack of posts regarding the limits of
>  PL/pgSQL 
> equate to it being adequate for most large applications.   

Yes, but without the posts, we don't know what's wrong, now, do we? 

 Postgres is an Open Source project.  We depend on the community to
 donate resources so that we can continue to offer a great database
 (IMHO, better than anything but Oracle and better than Oracle on a
 couple of issues) for free.  At a minimum, that participation must
 include providing detailed and well-considered requests for changes.
  Contributing code, documentation, and/or money is better and more
 likely to realize your goals.

Your post is extremely useful, and will no doubt be seized upon by Red
 Hat as strategic to their RHDB program if they know what's good for
 them.  However, it's a mistake to regard the Postgres project as if it
 was a vendor, from whom one expects program improvements just because
 one is a good customer. 

Frankly, considering the Oracle DBAs you refer to who can't even be
 bothered to join the mailing list ... I, for one, don't want them as
 part of the Postgres product and don't feel that there is any reason
 for the Postgres developers to consider their needs.    

For anyone else who is lurking on the mailing list, though ... SPEAK
 UP!  nobody will address your needs if you never communicate them.

-Josh Berkus


Thu, 04 Apr 2002 09:20:05 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: 16 parameter limit
John Proctor wrote:
>
> RE:  16 parameter limit
> 
> There was a message posted in March regarding this.   Bruce replied that this 
> issue did not come up often.   However, I think there is more to it than 
> that.   I think one reason that it does not come up is because most Oracle 
> DBAs are not going to dig through mailing lists and take the time to post 
> questions.   Once they discover that PL/pgSQL != PL/SQL they just move on.   

Actually, I said it didn't come up much, but I know of several heavy
PL/pgSQL users who do have trouble with the 16 parameter limit, and I am
looking into increasing it.  If someone wants to do some legwork, go
ahead.  I do think it needs to be increases.  The lack of complains
makes it hard for me to advocate increasing it, especially if there is a
disk space penalty, but personally, I do think it needs increasing.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 4 Apr 2002 21:40:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 16 parameter limit
Bruce,

> Actually, I said it didn't come up much, but I know of several heavy
> PL/pgSQL users who do have trouble with the 16 parameter limit, and I
>  am
> looking into increasing it.  If someone wants to do some legwork, go
> ahead.  I do think it needs to be increases.  The lack of complains
> makes it hard for me to advocate increasing it, especially if there
>  is a
> disk space penalty, but personally, I do think it needs increasing.

Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
 need to increase the *default* number of parameters.  Postgres just
 needs to implement a parameter number change as part of a documented
 command-line compile-time option, i.e. "--with-parameters=32".
  Currently, increasing the number of parameters requires altering the
 C config files before compilation, a rather user-hostile process.  

I've raised this point 3 or 4 times on this list now, and have not seen
 a respons from you or Thomas on this suggestion.   If I had the
 skills, I'd do it myself and upload the changes, but C is not my
 strong suit.

Also, what is the practical maximum number of parameters?

-Josh Berkus

Fri, 05 Apr 2002 08:30:08 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: 16 parameter limit
"Josh Berkus" <josh@agliodbs.com> writes:
> Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
>  need to increase the *default* number of parameters.  Postgres just
>  needs to implement a parameter number change as part of a documented
>  command-line compile-time option, i.e. "--with-parameters=32".

I would not object to providing such a configure option; it seems a
reasonable thing to do.  But the real debate here seems to be what
the default should be.  The ACS people would like their code to run
on a "stock" Postgres installation, so they've been lobbying to change
the default, not just to make it fractionally easier to build a
non-default configuration.

> Also, what is the practical maximum number of parameters?

If you tried to make it more than perhaps 500, you'd start to see
index-tuple-too-big failures in the pg_proc indexes.  Realistically,
though, I can't see people calling procedures with hundreds of
positionally-specified parameters --- such code would be unmanageably
error-prone.

I was surprised that people were dissatisfied with 16 (it was 8 not very
long ago...).  Needing more strikes me as a symptom of either bad coding
practices or missing features of other sorts.

			regards, tom lane

Fri, 05 Apr 2002 13:21:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 16 parameter limit 
Tom,

> I was surprised that people were dissatisfied with 16 (it was 8 not
>  very
> long ago...).  Needing more strikes me as a symptom of either bad
>  coding
> practices or missing features of other sorts.

No, not really.  It's just people wanting to use PL/pgSQL procedures as
 data filters.  For example, I have a database with complex
 dependancies and validation rules that I started under 7.0.3, when
 RULES were not an option for such things and triggers were harder to
 write.  As a result, I have the interface push new records for, say,
 the CLIENTS table through a PL/pgSQL procedure rather than writing to
 the table directly.  Since the table has 18 columns, I need (18 + 2
 for session & user) 20 parameters for this procedure.  

As John has discussed, this kind of data structure is relatively common
 in both Oracle and Informix shops.  As such, Postgres emulating this
 ability allows DBAs from those worlds to consider moving to Postgres
 and RHDB.   While the same kind of business logic can be implemented
 through Rules and Triggers, the Postgres structure for these things is
 unique and as a result not very portable.

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Fri, 05 Apr 2002 14:29:14 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: 16 parameter limit 
"Josh Berkus" <josh@agliodbs.com> writes:
> Tom,
>> I was surprised that people were dissatisfied with 16 (it was 8 not
>> very long ago...).  Needing more strikes me as a symptom of either bad
>> coding practices or missing features of other sorts.

> No, not really.  It's just people wanting to use PL/pgSQL procedures as
>  data filters.  For example, I have a database with complex
>  dependancies and validation rules that I started under 7.0.3, when
>  RULES were not an option for such things and triggers were harder to
>  write.  As a result, I have the interface push new records for, say,
>  the CLIENTS table through a PL/pgSQL procedure rather than writing to
>  the table directly.  Since the table has 18 columns, I need (18 + 2
>  for session & user) 20 parameters for this procedure.  

Yeah, but if we had slightly better support for rowtype parameters in
plpgsql, you could do it with *three* parameters: session, user, and
contents of record as a clients%rowtype structure.  And it'd probably
be a lot easier to read, and more maintainable in the face of changes
to the clients table structure.  This is why I say that needing lots
of parameters may be a symptom of missing features rather than an
indication that we ought to push up FUNC_MAX_ARGS.

			regards, tom lane

Fri, 05 Apr 2002 18:18:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 16 parameter limit 
Tom,

> Yeah, but if we had slightly better support for rowtype parameters in
> plpgsql, you could do it with *three* parameters: session, user, and
> contents of record as a clients%rowtype structure.  And it'd probably
> be a lot easier to read, and more maintainable in the face of changes
> to the clients table structure.  This is why I say that needing lots
> of parameters may be a symptom of missing features rather than an
> indication that we ought to push up FUNC_MAX_ARGS.

You're right for my databases.  For that matter, better support for
 rowtype is on the laundry list of PL/SQL compatibility issues.

However, we also want to support users who are porting their PL/SQL
 applications, which may not be easily translated into %rowtype
 paramters.  As I've said before, all this requires is a good
 compile-time option; increasing the default is unnecessary.

What do you (personally) think about trying to get RH involved in
 expanding PL/pgSQL's capabilites as a way fo targeting Oracle's users
 for RHDB?

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Fri, 05 Apr 2002 15:26:13 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: 16 parameter limit 
"Josh Berkus" <josh@agliodbs.com> writes:
> However, we also want to support users who are porting their PL/SQL
>  applications, which may not be easily translated into %rowtype
>  paramters.

Well, probably the $64 question there is: what is Oracle's limit on
number of parameters?

> What do you (personally) think about trying to get RH involved in
>  expanding PL/pgSQL's capabilites as a way fo targeting Oracle's users
>  for RHDB?

Seems like a good idea in the abstract ... but the hard question is what
are you willing to see *not* get done in order to put cycles on plpgsql.
And there's not a large supply of cycles.

			regards, tom lane

Fri, 05 Apr 2002 18:33:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 16 parameter limit 
Tom,

> Seems like a good idea in the abstract ... but the hard question is
>  what
> are you willing to see *not* get done in order to put cycles on
>  plpgsql.
> And there's not a large supply of cycles.

Well, it's back to the idea of raising money, then.

-Josh

Fri, 05 Apr 2002 15:51:35 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: 16 parameter limit 


Tom Lane wrote:
> "Josh Berkus" <josh@agliodbs.com> writes:
> 
>>However, we also want to support users who are porting their PL/SQL
>> applications, which may not be easily translated into %rowtype
>> paramters.
>
> Well, probably the $64 question there is: what is Oracle's limit on
> number of parameters?

According to the Oracle 9 documentation the limit for number of 
parameters to a function is 64K.

--Barry


Fri, 05 Apr 2002 21:35:49 -0800
Barry Lind <barry@xythos.com>
Re: 16 parameter limit
The following patch adds --maxindfuncparams to configure to allow you to
more easily set the maximum number of function parameters and columns
in an index.  (Can someone come up with a better name?)

The patch also removes --def_maxbackends, which Tom reported a few weeks
ago he wanted to remove.  Can people review this?  To test it, you have
to run autoconf.

Are we staying at 16 as the default?   I personally think we can
increase it to 32 with little penalty, and that we should increase
NAMEDATALEN to 64.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Josh Berkus" <josh@agliodbs.com> writes:
> > Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
> >  need to increase the *default* number of parameters.  Postgres just
> >  needs to implement a parameter number change as part of a documented
> >  command-line compile-time option, i.e. "--with-parameters=32".
> 
> I would not object to providing such a configure option; it seems a
> reasonable thing to do.  But the real debate here seems to be what
> the default should be.  The ACS people would like their code to run
> on a "stock" Postgres installation, so they've been lobbying to change
> the default, not just to make it fractionally easier to build a
> non-default configuration.
> 
> > Also, what is the practical maximum number of parameters?
> 
> If you tried to make it more than perhaps 500, you'd start to see
> index-tuple-too-big failures in the pg_proc indexes.  Realistically,
> though, I can't see people calling procedures with hundreds of
> positionally-specified parameters --- such code would be unmanageably
> error-prone.
> 
> I was surprised that people were dissatisfied with 16 (it was 8 not very
> long ago...).  Needing more strikes me as a symptom of either bad coding
> practices or missing features of other sorts.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: configure.in
===================================================================
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.178
diff -c -r1.178 configure.in
*** configure.in	14 Apr 2002 17:23:20 -0000	1.178
--- configure.in	16 Apr 2002 01:47:00 -0000
***************
*** 215,229 ****
  AC_SUBST(default_port)
  
  #
! # Maximum number of allowed connections (--with-maxbackends), default 32
  #
! AC_MSG_CHECKING([for default soft limit on number of connections])
! PGAC_ARG_REQ(with, maxbackends, [  --with-maxbackends=N    set default maximum number of connections [32]],
               [],
!              [with_maxbackends=32])
! AC_MSG_RESULT([$with_maxbackends])
! AC_DEFINE_UNQUOTED([DEF_MAXBACKENDS], [$with_maxbackends],
!                    [The default soft limit on the number of concurrent connections, i.e., the default for the postmaster -N switch (--with-maxbackends)])
  
  
  #
--- 215,229 ----
  AC_SUBST(default_port)
  
  #
! # Maximum number of index/function parameters (--with-maxindfuncparams), default 16
  #
! AC_MSG_CHECKING([maximum number of index/function parameters])
! PGAC_ARG_REQ(with, maxindfuncparams, [  --with-maxindfuncparams=N    maximum number of index/function parameters [16]],
               [],
!              [with_maxindfuncparams=16])
! AC_MSG_RESULT([$with_maxindfuncparams])
! AC_DEFINE_UNQUOTED([MAXINDFUNCPARAMS], [$with_maxindfuncparams],
!                    [The maximum number of index/function parameters])
  
  
  #
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.65
diff -c -r1.65 guc.c
*** src/backend/utils/misc/guc.c	3 Apr 2002 05:39:32 -0000	1.65
--- src/backend/utils/misc/guc.c	16 Apr 2002 01:47:02 -0000
***************
*** 408,419 ****
  	 */
  	{
  		"max_connections", PGC_POSTMASTER, PGC_S_DEFAULT, &MaxBackends,
! 		DEF_MAXBACKENDS, 1, INT_MAX, NULL, NULL
  	},
  
  	{
  		"shared_buffers", PGC_POSTMASTER, PGC_S_DEFAULT, &NBuffers,
! 		DEF_NBUFFERS, 16, INT_MAX, NULL, NULL
  	},
  
  	{
--- 408,419 ----
  	 */
  	{
  		"max_connections", PGC_POSTMASTER, PGC_S_DEFAULT, &MaxBackends,
! 		32, 1, INT_MAX, NULL, NULL
  	},
  
  	{
  		"shared_buffers", PGC_POSTMASTER, PGC_S_DEFAULT, &NBuffers,
! 		64, 16, INT_MAX, NULL, NULL
  	},
  
  	{
Index: src/include/pg_config.h.in
===================================================================
RCS file: /cvsroot/pgsql/src/include/pg_config.h.in,v
retrieving revision 1.21
diff -c -r1.21 pg_config.h.in
*** src/include/pg_config.h.in	10 Apr 2002 22:47:09 -0000	1.21
--- src/include/pg_config.h.in	16 Apr 2002 01:47:03 -0000
***************
*** 77,87 ****
  #undef DEF_PGPORT_STR
  
  /*
!  * Default soft limit on number of backend server processes per postmaster;
!  * this is just the default setting for the postmaster's -N switch.
!  * (--with-maxbackends=N)
   */
! #undef DEF_MAXBACKENDS
  
  /* --enable-nls */
  #undef ENABLE_NLS
--- 77,86 ----
  #undef DEF_PGPORT_STR
  
  /*
!  * The maximum number of columns in an index and the maximum number of
!  * parameters to a function.  This controls the length of oidvector.
   */
! #undef MAXINDFUNCPARAMS
  
  /* --enable-nls */
  #undef ENABLE_NLS
***************
*** 107,121 ****
   */
  
  /*
-  * Default number of buffers in shared buffer pool (each of size BLCKSZ).
-  * This is just the default setting for the postmaster's -B switch.
-  * Perhaps it ought to be configurable from a configure switch.
-  * NOTE: default setting corresponds to the minimum number of buffers
-  * that postmaster.c will allow for the default MaxBackends value.
-  */
- #define DEF_NBUFFERS (DEF_MAXBACKENDS > 8 ? DEF_MAXBACKENDS * 2 : 16)
- 
- /*
   * Size of a disk block --- this also limits the size of a tuple.
   * You can set it bigger if you need bigger tuples (although TOAST
   * should reduce the need to have large tuples, since fields can now
--- 106,111 ----
***************
*** 162,169 ****
   * switch statement in fmgr_oldstyle() in src/backend/utils/fmgr/fmgr.c.
   * But consider converting such functions to new-style instead...
   */
! #define INDEX_MAX_KEYS		16
! #define FUNC_MAX_ARGS		INDEX_MAX_KEYS
  
  /*
   * System default value for pg_attribute.attstattarget
--- 152,159 ----
   * switch statement in fmgr_oldstyle() in src/backend/utils/fmgr/fmgr.c.
   * But consider converting such functions to new-style instead...
   */
! #define INDEX_MAX_KEYS		MAXINDFUNCPARAMS
! #define FUNC_MAX_ARGS		MAXINDFUNCPARAMS
  
  /*
   * System default value for pg_attribute.attstattarget
Mon, 15 Apr 2002 22:58:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] 16 parameter limit
On the note of NAMEDATALEN, a view in the INFORMATION_SCHEMA
definition is exactly 2 characters over the current limit.

ADMINISTRABLE_ROLE_AUTHORIZATIONS

Not that it's a great reason, but it isn't a bad one for increasing
the limit ;)

--
Rod Taylor

> Are we staying at 16 as the default?   I personally think we can
> increase it to 32 with little penalty, and that we should increase
> NAMEDATALEN to 64.



Mon, 15 Apr 2002 23:19:45 -0400
"Rod Taylor" <rbt@zort.ca>
Re: [PATCHES] [SQL] 16 parameter limit
Bruce,

> The following patch adds --maxindfuncparams to configure to allow you
> to
> more easily set the maximum number of function parameters and columns
> in an index.  (Can someone come up with a better name?)

How about simply --max_params ?

> Are we staying at 16 as the default?   I personally think we can
> increase it to 32 with little penalty, 

I'd vote for that.  But then, you knew that.  John Proctor wants 128.

>and that we should increase
> NAMEDATALEN to 64.

I don't even know that is.  

-Josh

Mon, 15 Apr 2002 20:25:20 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: [SQL] 16 parameter limit
En Mon, 15 Apr 2002 23:19:45 -0400
"Rod Taylor" <rbt@zort.ca> escribiï¿½:

> On the note of NAMEDATALEN, a view in the INFORMATION_SCHEMA
> definition is exactly 2 characters over the current limit.
> 
> ADMINISTRABLE_ROLE_AUTHORIZATIONS
> 
> Not that it's a great reason, but it isn't a bad one for increasing
> the limit ;)

http://archives.postgresql.org/pgsql-general/2002-01/msg00939.php

(Tom Lane says both SQL92 and SQL99 specify 128 as the maximun
identifier length)

Anyway, how does one measure the perfomance impact of such a change?
By merely changing the constant definition, or also by actually using
long identifiers? I can do that if it's of any help, for various values
perhaps.

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Las cosas son buenas o malas segun las hace nuestra opinion" (Lisias)

Mon, 15 Apr 2002 23:34:04 -0400
Alvaro Herrera <alvherre@atentus.com>
Re: [PATCHES] [SQL] 16 parameter limit
Bruce Momjian writes:

> The following patch adds --maxindfuncparams to configure to allow you to
> more easily set the maximum number of function parameters and columns
> in an index.  (Can someone come up with a better name?)

> Are we staying at 16 as the default?   I personally think we can
> increase it to 32 with little penalty,

If you want to increase it, let's just increase it and not add any more
configure options.  If someone wants more than 32 then we really need to
start talking about design issues.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 15 Apr 2002 23:34:06 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: [SQL] 16 parameter limit
> > Are we staying at 16 as the default?   I personally think we can
> > increase it to 32 with little penalty,
>
> If you want to increase it, let's just increase it and not add any more
> configure options.  If someone wants more than 32 then we really need to
> start talking about design issues.

Why not give them the configure option?  It's not good HCI to impose
arbitrary limits on people...?

We can default it to 32, since there's demand for it.  If a particular user
decided to configure it higher, then they do that knowing that it may cause
performance degradation.  It's good to give them that choice though.

Chris


Tue, 16 Apr 2002 11:35:57 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] 16 parameter limit
On Mon, 15 Apr 2002 23:34:04 -0400
"Alvaro Herrera" <alvherre@atentus.com> wrote:
> En Mon, 15 Apr 2002 23:19:45 -0400
> "Rod Taylor" <rbt@zort.ca> escribiï¿½:
> 
> > On the note of NAMEDATALEN, a view in the INFORMATION_SCHEMA
> > definition is exactly 2 characters over the current limit.
> > 
> > ADMINISTRABLE_ROLE_AUTHORIZATIONS
> > 
> > Not that it's a great reason, but it isn't a bad one for increasing
> > the limit ;)
> 
> http://archives.postgresql.org/pgsql-general/2002-01/msg00939.php
> 
> (Tom Lane says both SQL92 and SQL99 specify 128 as the maximun
> identifier length)
> 
> Anyway, how does one measure the perfomance impact of such a change?
> By merely changing the constant definition, or also by actually using
> long identifiers?

Name values are stored NULL-padded up to NAMEDATALEN bytes, so
there is no need to actually use long identifiers, just change
the value of NAMEDATALEN, recompile and run some benchmarks
(perhaps OSDB? http://osdb.sf.net).

If you do decide to run some benchmarks (and some more data
would be good), please use the current CVS code. I sent in a
patch a little while ago that should somewhat reduce the
penalty for increasing NAMEDATALEN.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Mon, 15 Apr 2002 23:42:35 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: [PATCHES] [SQL] 16 parameter limit
Alvaro Herrera wrote:
> (Tom Lane says both SQL92 and SQL99 specify 128 as the maximun
> identifier length)
> 
> Anyway, how does one measure the perfomance impact of such a change?
> By merely changing the constant definition, or also by actually using
> long identifiers? I can do that if it's of any help, for various values
> perhaps.

I think I would measure disk size change in a newly created database,
and run regression for various values.  That uses a lot of identifier
lookups.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 15 Apr 2002 23:44:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] [SQL] 16 parameter limit
> > Anyway, how does one measure the perfomance impact of such a change?
> > By merely changing the constant definition, or also by actually using
> > long identifiers? I can do that if it's of any help, for various values
> > perhaps.
>
> I think I would measure disk size change in a newly created database,
> and run regression for various values.  That uses a lot of identifier
> lookups.

With schemas, maybe there'd be less name lookups and comparisons anyway,
since there's more reliance on oids instead of names?

Chris


Tue, 16 Apr 2002 11:49:30 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [PATCHES] [SQL] 16 parameter limit
On Tue, 16 Apr 2002 11:35:57 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> wrote:
> > > Are we staying at 16 as the default?   I personally think we can
> > > increase it to 32 with little penalty,
> >
> > If you want to increase it, let's just increase it and not add any more
> > configure options.  If someone wants more than 32 then we really need to
> > start talking about design issues.
> 
> Why not give them the configure option?  It's not good HCI to impose
> arbitrary limits on people...?

It's not an arbitrary limit -- users can easily change pg_config.h.

> We can default it to 32, since there's demand for it.  If a particular user
> decided to configure it higher, then they do that knowing that it may cause
> performance degradation.  It's good to give them that choice though.

What if someone actually uses functions with more than 32
arguments? Their code will not longer be portable among
PostgreSQL installations, and they'll need to get the local
admin to recompile.

I could see adding a configure option if there was a justifiable
reason for using functions with more than 32 arguments -- but
IMHO that is quite a bizarre situation anyway, as Peter said.

My vote is to set the default # of function args to some
reasonable default (32 sounds good), and leave it at that.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Mon, 15 Apr 2002 23:52:16 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: [SQL] 16 parameter limit
> What if someone actually uses functions with more than 32
> arguments? Their code will not longer be portable among
> PostgreSQL installations, and they'll need to get the local
> admin to recompile.
>
> I could see adding a configure option if there was a justifiable
> reason for using functions with more than 32 arguments -- but
> IMHO that is quite a bizarre situation anyway, as Peter said.
>
> My vote is to set the default # of function args to some
> reasonable default (32 sounds good), and leave it at that.

OK, agreed.  Then they at least are forced to write functions that will work
on all Postgres 7.3 and above...

Chris


Tue, 16 Apr 2002 11:57:04 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] 16 parameter limit
Neil Conway wrote:
> On Tue, 16 Apr 2002 11:35:57 +0800
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> wrote:
> > > > Are we staying at 16 as the default?   I personally think we can
> > > > increase it to 32 with little penalty,
> > >
> > > If you want to increase it, let's just increase it and not add any more
> > > configure options.  If someone wants more than 32 then we really need to
> > > start talking about design issues.
> > 
> > Why not give them the configure option?  It's not good HCI to impose
> > arbitrary limits on people...?
> 
> It's not an arbitrary limit -- users can easily change pg_config.h.

Let me just point out that you have to change pg_config.h.in and run
configure _or_ change pg_config.h and _never_ run configure again.  It
is this complexity that makes a configure option look acceptable.

Maybe we should pull some of the hard-coded, non-configure stuff from
pg_config.h into a separate file and just include it from pg_config.h.

> > We can default it to 32, since there's demand for it.  If a particular user
> > decided to configure it higher, then they do that knowing that it may cause
> > performance degradation.  It's good to give them that choice though.
> 
> What if someone actually uses functions with more than 32
> arguments? Their code will not longer be portable among
> PostgreSQL installations, and they'll need to get the local
> admin to recompile.


It is usually C++ overloading functions that use lots of args, or
functions that pass every table column into the function.  In those
cases, I can easily see 32 params.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 15 Apr 2002 23:57:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] 16 parameter limit
Peter,

> If you want to increase it, let's just increase it and not add any
> more
> configure options.  If someone wants more than 32 then we really need
> to
> start talking about design issues.

Actually, many Oracle DBAs use functions/procedures with up to 300
parameters.  If we want them to take PostgreSQL seriously as an
alternative to Oracle, we need to be able to accommodate that, at the
very least through an accessable configure-time option.

Also, this is a very frequent request on the SQL list.  The fact that
currently the defualt is 16 and pg_config.h is not documented anywhere,
is rather unfriendly to developers who like to use their functions as
pseudo-middleware.

John, please speak up here so the core team knows this isn't "just me."

-Josh Berkus

Mon, 15 Apr 2002 21:06:44 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: [SQL] 16 parameter limit
Neil Conway <nconway@klamath.dyndns.org> writes:
> My vote is to set the default # of function args to some
> reasonable default (32 sounds good), and leave it at that.

Bear in mind that s/32/16/ gives you the exact state of the discussion
when we raised the limit from 8 to 16 ;-)

Still, I do not really see the value of adding a configure argument.
Anyone who can't figure out how to tweak this in pg_config.h is probably
not ready to run a non-default configuration anyhow.

If the consensus is to raise the default from 16 to 32, I won't object.
Beyond that, I'd start asking questions about who's measured the
performance hit and what they found.

On the NAMEDATALEN part of the argument: SQL92 clearly expects that
NAMEDATALEN should be 128.  But the first report of the performance
cost looked rather grim.  Has anyone retried that experiment since
we tweaked hashname to not hash all the trailing zeroes?

			regards, tom lane

Tue, 16 Apr 2002 00:13:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] 16 parameter limit 
Tom Lane writes:

> Neil Conway <nconway@klamath.dyndns.org> writes:
> > My vote is to set the default # of function args to some
> > reasonable default (32 sounds good), and leave it at that.
>
> Bear in mind that s/32/16/ gives you the exact state of the discussion
> when we raised the limit from 8 to 16 ;-)

How about this:  We store the first 16 parameters in some fixed array for
fast access like now, and when you have more than 16 then 17 and beyond
get stored in some variable array in pg_proc.  This way procedures with
few arguments don't lose any performance but we could support an
"infinite" number of parameters easily.  It sounds kind of dumb, but
without some sort of break out of the fixed storage scheme we'll have this
argument forever.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 16 Apr 2002 00:36:11 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: [PATCHES] [SQL] 16 parameter limit 

Josh is exactly correct with regards to large oracle installs.  I personally 
have oracle functions that have around 70 to 80 params.   I saw some 
discussion that this is a design issue, as if to indicate design flaw.  
However, I think it is good design, based on the tools at hand.   I have 
complex transactions to create, some involve 10 to 15 large tables.  I also 
have requirements of being accessed via perl, python, c, zope, ruby, 
bash/sqlplus and possibly any other legacy app language that needs to 
interface.  Furthermore, I don't have time to teach every developer the 
details of the data model, the order of inserts, which columns to update 
under different conditions, etc.   I also don't have time to build a 
middleware interface in C and write wrappers in many languages.

My stored proc interface to a large and complex system is portable across any 
programming language that supports calling stored procs.  Furthermore, it 
shields the developers from what most don't even care about.  They know in 
the end, that if they pass the right data to my stored proc (which is usally 
just a hash of vars anyway, oracle supports pass by name) that all will be 
fine.  I also, know that I can change the implementation of the data model 
and as long as I keep the "interface" the same then perl, python, ruby, zope, 
etc all still work.  That is good design.  No sane DBA would give 
insert/update/delete permissions on any table to any user other than owner.  
That is the only way to guarantee data integrity.

I think some of the users here are coming from the perspective of simple 
dynamic web content or a small dev environment where all of the developers 
are multi-talented.  However, try an enterprise database that may have 200 to 
300 developers working on it over a 10 year lifetime or the merging of 
multiple very large clients into a common system.  I worked on the database 
for the Olympics in Atlanta and Nagano (about 200 developers in Atlanta).  
Database was DB/2 and all middleware in C.  What a nightmare.

Bottomline.  PL/SQL is one of the top reasons for Oracle's success.  If you 
are an Oracle shop then PL/SQL makes a better middleware layer than any other 
language. Simple, fast, stable, single point of entry.  What could be better.


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.  I 
am sorry if I irritated the group.   My only purpose for starting this was to 
help point out one of the top areas that PostgreSQL will need to address if 
it wants to succeed in the enterprise.  If that is not a goal, then my 
requests are probably not all that valid.


On Tuesday 16 April 2002 12:06 am, Josh Berkus wrote:
> Peter,
>
> > If you want to increase it, let's just increase it and not add any
> > more
> > configure options.  If someone wants more than 32 then we really need
> > to
> > start talking about design issues.
>
> Actually, many Oracle DBAs use functions/procedures with up to 300
> parameters.  If we want them to take PostgreSQL seriously as an
> alternative to Oracle, we need to be able to accommodate that, at the
> very least through an accessable configure-time option.
>
> Also, this is a very frequent request on the SQL list.  The fact that
> currently the defualt is 16 and pg_config.h is not documented anywhere,
> is rather unfriendly to developers who like to use their functions as
> pseudo-middleware.
>
> John, please speak up here so the core team knows this isn't "just me."
>
> -Josh Berkus

Mon, 15 Apr 2002 23:49:21 -0500
John Proctor <jproctor@prium.net>
Re: [SQL] 16 parameter limit
Tom,

> Still, I do not really see the value of adding a configure argument.
> Anyone who can't figure out how to tweak this in pg_config.h is
> probably
> not ready to run a non-default configuration anyhow.

I disagree *very* strongly.  Given that the documentation on
pg_config.h was removed from the idocs and that Pater has made noises
about removing pg_config.h entirely, it is not a substitute for
command-line configure options.

> If the consensus is to raise the default from 16 to 32, I won't
> object.
> Beyond that, I'd start asking questions about who's measured the
> performance hit and what they found.

If you can suggest a reasonable test, I will test this at 32, 64, 128
and 256 parameters to settle this issue.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Mon, 15 Apr 2002 21:50:21 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: [SQL] 16 parameter limit 
Peter Eisentraut <peter_e@gmx.net> writes:
> How about this:  We store the first 16 parameters in some fixed array for
> fast access like now, and when you have more than 16 then 17 and beyond
> get stored in some variable array in pg_proc.

<<itch>>  What's this going to cost us in the function lookup code paths?

If we can do it with little or no performance cost (at least for the
"normal case" of fewer-than-N parameters) then I'm all ears.

			regards, tom lane

Tue, 16 Apr 2002 01:01:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [PATCHES] [SQL] 16 parameter limit 
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > How about this:  We store the first 16 parameters in some fixed array for
> > fast access like now, and when you have more than 16 then 17 and beyond
> > get stored in some variable array in pg_proc.
> 
> <<itch>>  What's this going to cost us in the function lookup code paths?
> 
> If we can do it with little or no performance cost (at least for the
> "normal case" of fewer-than-N parameters) then I'm all ears.

OK, I have an idea.  Tom, didn't you just add code that allows the cache
to return multiple rows for a lookup?  I think you did it for schemas.

What if we lookup on the first 16 params, then look at every matching
hit if there are more than 16 params supplied?  Another idea would be to
hash the function arg types and look that up rather than looking for
exact matches of oidvector.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 16 Apr 2002 01:06:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] [SQL] 16 parameter limit
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

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Tue, 16 Apr 2002 12:04:37 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: [SQL] 16 parameter limit
On Tue, 2002-04-16 at 07:01, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > How about this:  We store the first 16 parameters in some fixed array for
> > fast access like now, and when you have more than 16 then 17 and beyond
> > get stored in some variable array in pg_proc.
> 
> <<itch>>  What's this going to cost us in the function lookup code paths?
> 
> If we can do it with little or no performance cost (at least for the
> "normal case" of fewer-than-N parameters) then I'm all ears.

Perhaps we could use the 16-th element as an indicator of 16-or-more
args. If it is 0 then there are <= 15 args if it is something else, then
this something else is hash of extra argument types that need to be
looked up separately. 

Of course we will need some way of resolving multiple hash matches.

--------------
Hannu



16 Apr 2002 21:12:52 +0200
Hannu Krosing <hannu@tm.ee>
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

Wed, 17 Apr 2002 01:22:14 -0500
John Proctor <jproctor@prium.net>
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@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
>


Wed, 17 Apr 2002 22:29:12 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [SQL] 16 parameter limit
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


17 Apr 2002 09:41:29 -0500
Larry Rosenman <ler@lerctr.org>
Re: [SQL] 16 parameter limit
Folks,

> 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.

Also:

10) Allow declaration of all PostgreSQL data types, including custom
data types and domains, inside functions.  Especially important are
Arrays, which are supported as parameters but not as declarations.

11) PL/pgSQL has functionality 100% analagous to cursors, with a
different syntax.  While the PL/pgSQL record loop is easier to use, the
lack of support for standard cursor syntax mars the poratbility of
Oracle procedures to Postgres and vice-versa.

12) The biggie:  Allowing the easy return of query results from a
procedure.  This is currently supported through a rather difficult
workaround involving either the ROWTYPE datatype or a return Cursor.
 Both approaches require the use of a procedural code loop on the
interface side to read the data being returned ... much clumsier than
just dumping the data ala PL/SQL or T-SQL.  If implemented, this rowset
return would the the difference between a CREATE FUNCTION and a CREATE
PROCEDURE statement.

13) Allow the creation of multiple output parameters for PROCEDURES (as
opposed to FUNCTIONS) in the parameter declaration.

14) Procedures should have their own permissions, which supercede the
permissions on the tables being affected if the procedure is created by
the database owner, in the same way that Views can allow users to
Select data they would not be entitled to from the base tables.  In
other words, if I declare "GRANT SELECT ON fn_modify_assignment TO
phpaccess", the user phpaccess should be able to run
fn_modify_assignment even if that user has no permissions on the
assignment table itself.

-Josh Berkus

P.S. I haven't brought up these issues before because there is no way I
can contribute any significant resources to completing them.  

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Wed, 17 Apr 2002 09:08:58 -0700
"Josh Berkus" <josh@agliodbs.com>
Re: [SQL] 16 parameter limit

Added to TODO:

        o Improve PL/PgSQL exception handling
        o Allow PL/PgSQL parameters to be specified by name and type during
          definition
        o Allow PL/PgSQL function parameters to be passed by name,
          get_employee_salary(emp_id => 12345, tax_year => 2001)
        o Add PL/PgSQL packages

> 
> 
> OK, here goes.
> 
> 1) More than 16 parameters. ? This can be parameter configurable if 
> necessary, but up to 128 would cover 99.9%.

Done to 32.

> 
> 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.

Added.

> 
> 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.

When we have subtransactions, we will be able to do this.

> 
> 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.

Same.

> 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.

This is pretty hard, especially because we have plug-in languages.  I
don't see a way to do this.

> 
> 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.

Added,

> 
> 7) Allow function parameters to be passed by name, not just positional. ?i.e. 
> get_employee_salary(emp_id => 12345, tax_year => 2001).

Added.
 
> 
> 8) Add packages. ?This is a great way to group related functions, create 
> reusable objects, like cursors, etc.

Added.

> 
> 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.

I don't see the point here, except perhaps you want TEMP functions?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 13 Aug 2002 22:43:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] 16 parameter limit

Added:

        o Allow array declarations and other data types in PL/PgSQl
          DECLARE
        o Add PL/PgSQL PROCEDURES that can return multiple values

> Also:
> 
> 10) Allow declaration of all PostgreSQL data types, including custom
> data types and domains, inside functions.  Especially important are
> Arrays, which are supported as parameters but not as declarations.


Added

> 11) PL/pgSQL has functionality 100% analagous to cursors, with a
> different syntax.  While the PL/pgSQL record loop is easier to use, the
> lack of support for standard cursor syntax mars the poratbility of
> Oracle procedures to Postgres and vice-versa.

Is this done?

> 
> 12) The biggie:  Allowing the easy return of query results from a
> procedure.  This is currently supported through a rather difficult
> workaround involving either the ROWTYPE datatype or a return Cursor.
>  Both approaches require the use of a procedural code loop on the
> interface side to read the data being returned ... much clumsier than
> just dumping the data ala PL/SQL or T-SQL.  If implemented, this rowset
> return would the the difference between a CREATE FUNCTION and a CREATE
> PROCEDURE statement.

Done for 7.3.


> 
> 13) Allow the creation of multiple output parameters for PROCEDURES (as
> opposed to FUNCTIONS) in the parameter declaration.

Added.

> 14) Procedures should have their own permissions, which supercede the
> permissions on the tables being affected if the procedure is created by
> the database owner, in the same way that Views can allow users to
> Select data they would not be entitled to from the base tables.  In
> other words, if I declare "GRANT SELECT ON fn_modify_assignment TO
> phpaccess", the user phpaccess should be able to run
> fn_modify_assignment even if that user has no permissions on the
> assignment table itself.

Done, I think, for 7.3.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 13 Aug 2002 22:48:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] 16 parameter limit
Bruce Momjian wrote:
>>12) The biggie:  Allowing the easy return of query results from a
>>procedure.  This is currently supported through a rather difficult
>>workaround involving either the ROWTYPE datatype or a return Cursor.
>> Both approaches require the use of a procedural code loop on the
>>interface side to read the data being returned ... much clumsier than
>>just dumping the data ala PL/SQL or T-SQL.  If implemented, this rowset
>>return would the the difference between a CREATE FUNCTION and a CREATE
>>PROCEDURE statement.
> 
> 
> Done for 7.3.

Um, not done yet (PL/pgSQL table functions). Currently only SQL and C 
functions supported. I've had an off-line discussion with Neil, and I 
think he is working this item and plans to have it ready for 7.3.

CREATE PROCEDURE is not planned for 7.3 at all (I don't think; see the 
CALL foo recent discussion).

It's not clear to me which one is meant by the above. "Dumping the data 
ala PL/SQL or T-SQL" could mean either. PL/SQL supports table functions; 
T-SQL only supports the CALL foo type capability. See:
http://archives.postgresql.org/pgsql-general/2002-08/msg00602.php
for a description of the difference.

Joe


Tue, 13 Aug 2002 20:28:52 -0700
Joe Conway <mail@joeconway.com>
Re: [SQL] 16 parameter limit
Joe Conway wrote:
> Bruce Momjian wrote:
> >>12) The biggie:  Allowing the easy return of query results from a
> >>procedure.  This is currently supported through a rather difficult
> >>workaround involving either the ROWTYPE datatype or a return Cursor.
> >> Both approaches require the use of a procedural code loop on the
> >>interface side to read the data being returned ... much clumsier than
> >>just dumping the data ala PL/SQL or T-SQL.  If implemented, this rowset
> >>return would the the difference between a CREATE FUNCTION and a CREATE
> >>PROCEDURE statement.
> > 
> > 
> > Done for 7.3.
> 
> Um, not done yet (PL/pgSQL table functions). Currently only SQL and C 
> functions supported. I've had an off-line discussion with Neil, and I 
> think he is working this item and plans to have it ready for 7.3.

OK, added to 7.3 open items:

	Allow PL/PgSQL functions to return sets

> CREATE PROCEDURE is not planned for 7.3 at all (I don't think; see the 
> CALL foo recent discussion).

Right, on TODO.

> It's not clear to me which one is meant by the above. "Dumping the data 
> ala PL/SQL or T-SQL" could mean either. PL/SQL supports table functions; 
> T-SQL only supports the CALL foo type capability. See:
> http://archives.postgresql.org/pgsql-general/2002-08/msg00602.php
> for a description of the difference.

Not sure.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 13 Aug 2002 23:36:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] 16 parameter limit
Bruce Momjian wrote:
> 
> Added to TODO:
> 
>         o Improve PL/PgSQL exception handling

Exception handling? You're talking about nested transaction support and
catchable errors in the first place, and then (a year later) making use
of that functionality in the procedural languages, right?

>         o Allow PL/PgSQL parameters to be specified by name and type during
>           definition
>         o Allow PL/PgSQL function parameters to be passed by name,
>           get_employee_salary(emp_id => 12345, tax_year => 2001)

CREATE FUNCTION is in no way PL/pgSQL specific. PL/pgSQL simply works
around that lack with the ALIAS syntax in the DECLARE section.

>         o Add PL/PgSQL packages

This really is a 100% PL/PgSQL problem.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Wed, 14 Aug 2002 14:30:53 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: [SQL] 16 parameter limit
Jan Wieck wrote:
> Bruce Momjian wrote:
> > 
> > Added to TODO:
> > 
> >         o Improve PL/PgSQL exception handling
> 
> Exception handling? You're talking about nested transaction support and
> catchable errors in the first place, and then (a year later) making use
> of that functionality in the procedural languages, right?

Uh, I guess.  Not sure.

> 
> >         o Allow PL/PgSQL parameters to be specified by name and type during
> >           definition
> >         o Allow PL/PgSQL function parameters to be passed by name,
> >           get_employee_salary(emp_id => 12345, tax_year => 2001)
> 
> CREATE FUNCTION is in no way PL/pgSQL specific. PL/pgSQL simply works
> around that lack with the ALIAS syntax in the DECLARE section.

Text updated to:

        o Allow parameters to be specified by name and type during      
          definition
        o Allow function parameters to be passed by name,
          get_employee_salary(emp_id => 12345, tax_year => 2001)

> >         o Add PL/PgSQL packages
> 
> This really is a 100% PL/PgSQL problem.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 14 Aug 2002 14:34:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] 16 parameter limit