16 parameter limit
John Proctor
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Josh Berkus
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Tom Lauren
Josh,
> 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.
Hmmmm.. I don´t know, this would be a almost "religious" discussion, I
personally had some "fights" of opinions.
But, for me I´ve concluded: If the business logic isn´t in the DB, there is
a GAP between the data itself and the correct interpretation of its
structure.
So for me, data isn´t just a faceless pile of useful pieces, the _meaning_
makes it useful. Ok, SOMEWHERE i´ll have to put it together. But where?
Definitely there, where the source is. Moving the logic out to Java or
whatever, cuts the "body" into two pieces.
Having a set of procedures (my fixed interface to the data), it´s no matter
anymore whether I use .net first and want to move to Java.
The middle tier then is what it really should be: the one to assemble the
page/windows, buttons, events, files, graphics to a presentable _product_.
And not having to care, if this call is allowed or that statement parameter
is needed. Just connect to the database, call the defined proc´s, feed them
with your parameters and show the result in a appropriate form.
New developement platform (or, better, adding a second application that uses
exactly the same data but for different use) isn´t a problem then.
For example, an internet app done with java, for mass use, and a dedicated
gui-tool to do the same thing locally.
As long as I don´t change the interface, I can manage the db without
recompiling the programs. If one 'select' changes (changing efficiency
f.e.), I can do it _live_ within the db - no one will even notice.
Second thought: I believe it´s still a security problem. Having the plain
data without a defined calling mechanism, it´s accessible for ....
Ok it might sound a bit pessimistic, but that´s what I have to assume!
Always the worst! Example: for a web-application it´s ok to have a
"standard" user, say 'public'. I need to know the password in my app to
connect, so it´s useful to have just one. Assuming, someone steals my db
(*laughter*?) or gains acces somehow, and got the standard pass, he could
read everything just trying long enough. Maybe even knowing the structure!
Then it´s an open book for 'him'.
If I manage the user authentification/logic within my db, it´s a black box
seen from outside. Hm?
Why am I thinking so?
Well the truth is clear: When developing a product, there is no natural cut
between data, logic and representation. At design time. What a pleasure it
would be to design an object (interaction) model, and >storing,
finding,sorting< these objects were 'simple' methodes. That would be very
close to the Original Thought.
You might say there are object-oriented databases allready, but none I know
of I could afford. So there is a historical line between data and code we
have to deal with. My intention is to move this line to a more logical point
;)
This is why I would love Postgresql to have 'some' of the Transact-SQL
things that are very helpful. First - and as I read the lists not only me -
the trouble with the procedures, and I´ve not checked but I believe there
are no local variables?. I´m not a C coder so I can´t contribute even if I
would love to, and I have _absolutely_ no idea how complex this would be to
add :
CREATE PROCEDURE [db.][owner.][procname] { @param1 type {= default}
{output} | {, ....}| ... } AS
...statements / blocks / loops / selects / returns / etc....
GO:
This is how ms sql server does it, and -- i don´t like ms ;) -- it´s rather
fine:
_________________________________________________________
CREATE PROCEDURE my_first_proc @param_i int = 0, @param_s varchar(64),
@param_out int OUTPUT, @param_t datetime OUTPUT
AS
DECLARE @foo int /*declaring a locale variable */
SET @foo = 123; /* and setting some value */
SET/SELECT @param_out = (SELECT someother_id FROM table WHERE the_real_id =
@param_i)
/* setting the variable to the resulting value of the select, and after the
execution of my_first_proc the value is given back ;) and there could be
more than one of them!!*/
IF EXISTS (SELCET id FROM restrictions WHERE id=@foo)
BEGIN
SELECT name, pcs , price FROM table2 WHERE (id = @param_i) AND (name
LIKE @param_s )
/* the result is presented like any select */
-- do something
END
ELSE RETURN -1234
/* return a custom return-code to the calling "exec", just like functions
do, but always just int (for error codes) */
SET @param_t = getdate()
/* or now() in pg, will be returned too! */
GO:
_______________________________________________________
Just this ;)
If somebody will get this managed with Postgresql, it would be a good reason
for many ( I suppose.....) to completely change to Postgresql!!!
And, procedures are much faster than "assembled select-statement-strings" in
my program as they are "pre-compiled" and optimized, they can even be
re-optimized during server running ! (?)
> 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.
That´s a serious issue. I don´t know many C programmers anymore! ;)
But it´s true, it´s open source, and I am not forcing or begging, it´s just
what I´ve first noticed what makes it impossible to me to use PostgreSQL in
a commercial product.....but things change, don´t they? ;)
> 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.
A documentation is fine, true, but nothing compares to the interaction of
users! If they can´t get into it, their knowledge is dead.....
> > I think that the limitations of PL/pgSQL is a huge factor in people
> > not being
> > able to use Postgres instead of Oracle.
Some maybe, but not many...but still some.....
> > 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.
My point!
> I've done this on a smaller scale with Postgres + PHP. It's a good
> rapid development approach for intranet apps, and relatively secure.
Don´t agree, I wouldn´t use PHP even if I had to. What for? For beeing a
plaintext file? For trying to re-invent classes? For using another cryptic
language?
Secure? Hmmm I don´t know, I think it´s more for
home-developing.....PersonalHomePage ;)
> 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.
Hmm. To expand the language on a paper would be a pleasure, but hiding from
the programmer to implement it would be hard :)
> 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.
*sniff* it´s sad, I could only imagine students and
related-job-spare-time-freaks, but it´s a general problem......
> Here, I disagree. I am a certified MS SQL Server admin, and PL/pgSQL
> is already miles ahead of Transact-SQL.
Really? Hmm I´m not THAT inside ms sql server, could you give some thoughts
about this? Would be interesting to me....
> 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.
Ok, I hope my statement above is detailed enough ;)
> For anyone else who is lurking on the mailing list, though ... SPEAK
> UP! nobody will address your needs if you never communicate them.
spoke!
p.s. excuse me if I wrote something strange or untrue, it´s rather late now
and I´ll perform a join with my bed now......
CU Tom
Bruce Momjian
>
> 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
> 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
pg...@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Josh Berkus
> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Josh Berkus
> Thanks for replying. I fear that I may have come off as whiny or
>
> ungrateful. I hope that I did not. I am extremely happy with
> PostgreSQL
> and would like to see it take root in enterprise business.
Sorry. I tried to seperate my criticisms of the people you were
talking about (e.g. Oracle DBAs who can't be bothered to read the
mailing list) and you (who obviously *can* be bothered to read the
mailing list). Maybe *I* wasn't clear enough.
>I work
> with
> oracle, sybase and mssql every day. Oracle is a nice database, but
> it is
> expensive and I am not interested in their new direction (Java this,
> Java
> that). 9i has about 2GB of jar files that I don't even want or
> need, but I
> still have to back them up because I am afraid to delete them.
Hey! I like Java, personally. It's just that I don't often get
projects that are more intersted in comprehensive design as they are
in cost savings ... thus I do a *lot* of PL/pgSQL + PHP combinations.
> Anyway, I understand your point about using a 3GL for the middle
> tier data
> access, but I don't see that happening on large projects. I know
> there are
> many java projects that did that and still do, but I haven't seen
> many that
> worked out well. Most large projects that I have seen and I am
> involved in
> use a complete stored procedure interface. The reasons are simple.
> The
> DBAs can design the database and provide the interface. The
> developers only
> need to understand the interface exposed by the DBA (stored procs).
> It also
> allows for multiple languages to use the same interface, which is
> very
> important when integrating with legacy systems. Also, compiled
> PL/SQL runs
> much faster than java/jdbc, and is much faster to develop and debug.
> I can
> also minimize bugs by not allowing developers to write directly to
> tables.
> I know that if my procedure interface is correct and few changes are
> needed
> to the data layer then changes to the display layer do not effect the
>
> database. However, this same logic does not seem to hold well with
> the
> middle layer. Middle layers tend to follow changes in the display
> layer
> which increases potential for bugs.
Well, that's not the theory of n-tier development, but I'll agree that
real, CORBA-compliant business objects are seldom implemented properly
outside of (perpetually unfinished) Open Source projects. Certainly
the advantage of a SQL scripting language is that the person who
understands the database best (the DBA) can implement business rules
without learning a completely different programming lanuguage. This
was the reason for the termendous popularity of 4GL in its day (BTW,
there is a 4GL interpreter for PostgreSQL).
> It has been my experience that most large oracle projects done by
> good
> professionals use PL/SQL extensively. That is why I think
> PostgreSQL will
> not penetrate this market unless it can allow developers to continue
> using a
> model that has proven to be successful.
Yup. You gotta do what the customers want, or you don't capture them.
Let me also reiterate my statement that I believe that Great Bridge
made a mistake trying to take on Oracle last year, and that Red Hat is
more astute going after the MS SQL market.
> Please note that I am not someone just sitting around waiting for
> others to
> spend their time building my needs. I am involved in some other
> projects
> regarding open source. I am also not complaining. I just didn't
> want
> people to think that lack of questions regarding this meant lack of
> need in
> large companies. It is just that those people are not even looking
> at
> PostgreSQL right now. My only reason for bringing it up is my
> desire to see
> PostgreSQL move forward and so that I may (for selfish reasons) use
> it in
> some professional projects one day.
See my first paragraph.
If you want to pursue this (and I wish you would!) there's two ways to
go:
1. Try to get the current major commercial PostgreSQL implementation,
Red Hat's RHDB, interested in your suggestions. Michael Evans at Red
Hat is in charge of RHDB. As far as I know, however, all of their
development effort is cocentrated on the issues of replication, user
interface, and backup/recovery.
2. Work with me and other PL/pgSQL users to develop a laundry list of
PL/pgSQL improvements, and then recruit or hire a programmer or
programmers to implement them. I would be happy to contribute specs
and testing to such a project, but I don't know enough C to do
anything useful. Preferably, we would find someone interested in
being the ne PL/pgSQL lead so that the language can continue to move
forward indefinitely with a strong advocate and targeted goals for
every Postgres release.
-Josh Berkus
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Tom Lane
> 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
Josh Berkus
> 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 jo...@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Josh Berkus
> 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 jo...@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Josh Berkus
> 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
Barry Lind
Tom Lane wrote:
> "Josh Berkus" <jo...@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