Wishlist?
Hi everyone! I'd like to post a few features I'd like to see in coming
releases. Does anyone know about a wishlist newsgroup or web page, or
whoat is the proper way to propose such requests?
Thanks,
Ezequiel Tolnay
On Wed, Jul 20, 2005 at 02:44:19PM +1000, Ezequiel Tolnay wrote:
Hi everyone! I'd like to post a few features I'd like to see in coming
releases. Does anyone know about a wishlist newsgroup or web page, or
whoat is the proper way to propose such requests?
http://www.postgresql.org/developer/roadmap
Note especially the part about developers scratching their own itch. You
can request all you want, but unless a number of developers agree it's a
good idea it probably won't make it to the TODO. And stuff can get
pulled from the TODO without actually being done.
You'll have better luck getting something added if you're willing to
commit to developing it (or pay someone else to).
Having said all that, if you want to just request stuff (that's not
already on the TODO), you can just post it here and it might get on the
TODO if there's enough interest. If you want to discuss specifics,
-hackers is probably a better place.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
Jim C. Nasby wrote:
http://www.postgresql.org/developer/roadmap
Note especially the part about developers scratching their own itch. You
can request all you want, but unless a number of developers agree it's a
good idea it probably won't make it to the TODO. And stuff can get
pulled from the TODO without actually being done.You'll have better luck getting something added if you're willing to
commit to developing it (or pay someone else to).Having said all that, if you want to just request stuff (that's not
already on the TODO), you can just post it here and it might get on the
TODO if there's enough interest. If you want to discuss specifics,
-hackers is probably a better place.
Thanks a lot, I've read the TODO and have now an idea of what is the
current trend or plans for future releases.
Regarding my own needs, I'd be happy to "scratch my own itch", but
perhaps I should find some consensus before I engage myself in the task
of developing. And I would also need a little guidance in regards to how
to submit changes and all that.
I am a long time user of MS SQL Server (6.5, 7, and 2000) and recently
started employing PostgreSQL 8 (Windows version) for some projects, with
aims of replacing the SQL Server entirely soon. But the migration was
everything but smooth, even though SQL Server, in comparison with other
RDBMSs like Oracle, is fairly simple. So I would like to know how
populate would be the following additions to make PostgreSQL more
feature-compatible with SQLServer, for the future and current converts
like myself:
* Stored procedures: Although similar to functions, SP always return an
execution status rather than a result, but provide the caller with the
equivalent of running several sql instructions, as if running a script.
Unlike functions, their results cannot be utilised by most other
statements, with the exception of INSERT [...] EXECUTE [...]. Their main
attractive is to simplify the detachment between interface and
implementation. Perhaps this could be achieved extending some features
of functions, perhaps enabling a combination of SETOF result (which
needs an externally defined result type) and OUT parameters (which is
flexible but restricted to a single row), or handled similarly as the
current possibility to return cursors.
* Scripting language: A very powerful feature in SQLServer is its
support of variables and control structures embedded in the SQL
language, thus allowing running complex scripts without the need to pack
them in a function and then running the function. This is especially
limiting in PG since it doesn't support the creation of temporary
functions. In MSSSQL, variables are prefixed with AT (@) symbols, must
be declared before they're used, live only within the prepared statement
and can be set using SET, SELECT and UPDATE. Also, the conditional
statements are only IF and WHILE, they support subqueries and function
calls in their expressions, and group statements using BEGIN and END. I
find prefixing variables thus is a lot less confusing and error prone
than the parameter alias and variables used in PL/pgSQL. For the
implementation on PG, I believe that perhaps it would be nice to use
curly brackets instead of BEGIN and END.
* Inter-database access: Although PG greatly benefits from the use of
schemes, many times different applications are required to use separate
databases but still sharing some resources. The only current facility
that I'm aware of for this purpose in PG is through pl-perl functions,
but this prevents reusing current credentials and greatly reduce
flexibility.
I'd like also to propose the following features:
* Allow FETCH command to be used with CREATE TABLE tab AS qry (in place
of qry)
* Allow to optionally prevent overloading in functions, thus allowing
the CREATE OR REPLACE command replace the function despite having
different parameters
* Set the SERIAL automatically generated sequences as system objects,
thus allowing them to be hidden in the admin GUIs
* Allow a function's result type to be defined in the function itself,
similarly to the way OUT parameters are constructed. If a type for it
must be created for this purpose, set it as a system object and handle
its recreation automatically upon CREATE OR REPLACE FUNCTION to avoid
the current catch-22 dependency issues. Or perhaps let function
returning a SETOF RECORD to have an implicit (undefined) result type,
thus avoiding having to specify the type dynamically in the caller
statement SELECT func(...) AS (...)
* Create a ROWSET variable type in plpgsql that would function like a
temporary but in-memory table. Support for it would have to be added for
plpgsql's SELECT, UPDATE and DELETE statements.
* Make temporary schemes visible (and accessible) only to its current
owner/user, and create a tool to clear currently unused temporary
schemes (for instance during backups, or when duplicating a database)
* Allow inserting new columns in arbitrary positions with ALTER TABLE
tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like mysql does.
I hope a few of you will be interested in my proposals, so they get into
the TODO list. Once there, I'd be happy to contribute with some
development myself (either for these or other TODO items). I'm not a
proficient hacker with lots of time available, but I believe I'm a
fairly good programmer and I'm a Gentoo user so I have everything needed
to compile and build. But I believe I'll require a few months of
studying the code alone before I dare making any additions. So any hints
of how to become a PG developer to submit changes, and where to start
(or what documentation to read first) to have a fast head-start will be
highly appreciated.
Cheers,
Ezequiel Tolnay
On Thu, Jul 21, 2005 at 19:10:03 +1000,
Ezequiel Tolnay <mail@etolnay.com.ar> wrote:
* Stored procedures: Although similar to functions, SP always return an
execution status rather than a result, but provide the caller with the
equivalent of running several sql instructions, as if running a script.
Unlike functions, their results cannot be utilised by most other
statements, with the exception of INSERT [...] EXECUTE [...]. Their main
attractive is to simplify the detachment between interface and
implementation. Perhaps this could be achieved extending some features
of functions, perhaps enabling a combination of SETOF result (which
needs an externally defined result type) and OUT parameters (which is
flexible but restricted to a single row), or handled similarly as the
current possibility to return cursors.
Postgres' functions are already pretty powerful and cover most of what
stored procedures do. People are working on a version of stored procedures
that is closer to the standard and that should ease conversions. Some of
this is going to be in 8.1. Other stuff is planned for the future. You
can probably get more details by looking through the -hackers archives.
* Inter-database access: Although PG greatly benefits from the use of
schemes, many times different applications are required to use separate
databases but still sharing some resources. The only current facility
that I'm aware of for this purpose in PG is through pl-perl functions,
but this prevents reusing current credentials and greatly reduce
flexibility.
You may want to look at the dblink contrib module. I think it does some
of what you want.
* Allow to optionally prevent overloading in functions, thus allowing
the CREATE OR REPLACE command replace the function despite having
different parameters
What happens if there is more than one existing function with that name
already. Do all of the old functions get deleted?
Bruno Wolff III <bruno@wolff.to> writes:
On Thu, Jul 21, 2005 at 19:10:03 +1000,
* Allow to optionally prevent overloading in functions, thus allowing
the CREATE OR REPLACE command replace the function despite having
different parameters
What happens if there is more than one existing function with that name
already. Do all of the old functions get deleted?
What happens if there are existing references to the function using the
old parameter list?
The current approach is that if you want to change the parameter list or
result type, you have to issue DROP FUNCTION, with the resulting cascade
of any dependent objects. I think trying to change that would be a very
bad idea. It'd create a real mess in terms of consistency of views, and
what does it really buy you notationally over DROP/CREATE?
regards, tom lane
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote:
* Stored procedures: Although similar to functions, SP always return an
execution status rather than a result, but provide the caller with the
equivalent of running several sql instructions, as if running a script.
Unlike functions, their results cannot be utilised by most other
statements, with the exception of INSERT [...] EXECUTE [...]. Their main
attractive is to simplify the detachment between interface and
implementation. Perhaps this could be achieved extending some features
of functions, perhaps enabling a combination of SETOF result (which
needs an externally defined result type) and OUT parameters (which is
flexible but restricted to a single row), or handled similarly as the
current possibility to return cursors.
I'm not understanding the difference here. pl/pgsql can run just about
anything, including table creates/drops. Is it the return type you're
worried about. In mine I just return TRUE generally...
* Set the SERIAL automatically generated sequences as system objects,
thus allowing them to be hidden in the admin GUIs
They are already marked as system objects. Tables are 'r', sequences
'S', indexes 'i', etc... Perhaps you should open a bug on whatever
admin gui you're using to hide sequences.
* Create a ROWSET variable type in plpgsql that would function like a
temporary but in-memory table. Support for it would have to be added for
plpgsql's SELECT, UPDATE and DELETE statements.
How is this different from a temporary table?
* Make temporary schemes visible (and accessible) only to its current
owner/user, and create a tool to clear currently unused temporary
schemes (for instance during backups, or when duplicating a database)
Temporary schemas? Temporary tables go into a tempoary schema that is
automatically removed when you disconnect. What would more temporary
schemas acheive?
* Allow inserting new columns in arbitrary positions with ALTER TABLE
tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like mysql does.
This wouldn't be too hard I think, but maybe I'm missing something...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
On Thu, 21 Jul 2005, Martijn van Oosterhout wrote:
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote:
* Create a ROWSET variable type in plpgsql that would function like a
temporary but in-memory table. Support for it would have to be added for
plpgsql's SELECT, UPDATE and DELETE statements.How is this different from a temporary table?
I'd expect they'd scope "correctly" in cases of recursive calls and the
like. So, if the recursive call wanted its own, it wouldn't have to try to
find an unused temporary table name. Offhand, I'm not sure if that'd
really be useful in practice once temporary tables in plpgsql didn't
require one to jump through hoops, but it has some niceness.
Hmm, true. So I guess the real solution (once temp tables work nicely)
would be something like CREATE LOCAL TEMP TABLE where the pl/pgsql
parser invents the unusual name for you. Ofcourse, then you run into
the issue where subcalls (like using EXECUTE) won't be able to see that
table either.
What you want is lexical scoping between the pl/pgsql and the backend,
which is not really possible in the current system.
Have a nice day,
On Thu, Jul 21, 2005 at 08:12:11AM -0700, Stephan Szabo wrote:
On Thu, 21 Jul 2005, Martijn van Oosterhout wrote:
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote:
* Create a ROWSET variable type in plpgsql that would function like a
temporary but in-memory table. Support for it would have to be added for
plpgsql's SELECT, UPDATE and DELETE statements.How is this different from a temporary table?
I'd expect they'd scope "correctly" in cases of recursive calls and the
like. So, if the recursive call wanted its own, it wouldn't have to try to
find an unused temporary table name. Offhand, I'm not sure if that'd
really be useful in practice once temporary tables in plpgsql didn't
require one to jump through hoops, but it has some niceness.---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Tom Lane wrote:
Bruno Wolff III <bruno@wolff.to> writes:
What happens if there is more than one existing function with that name
already. Do all of the old functions get deleted?What happens if there are existing references to the function using the
old parameter list?The current approach is that if you want to change the parameter list or
result type, you have to issue DROP FUNCTION, with the resulting cascade
of any dependent objects. I think trying to change that would be a very
bad idea. It'd create a real mess in terms of consistency of views, and
what does it really buy you notationally over DROP/CREATE?
What I see is that the overloading is a very cool feature, but is not
necessary in most cases, and it introduces unnecessary administration
hassles. If there would be an attribute stating if a procedure has been
created overlodable (enabled by default, for backwards-compabitility),
then CREATE OR REPLACE would always replace the current one, regardless
of the parameters, and DROP would not require any parameters at all, and
a creation of a function with the NOOVERLOAD attribute would not be
allowed if there is already a function marked for overloading.
Cheers,
Ezequiel Tolnay
Martijn van Oosterhout wrote:
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote:
* Make temporary schemes visible (and accessible) only to its current
owner/user, and create a tool to clear currently unused temporary
schemes (for instance during backups, or when duplicating a database)Temporary schemas? Temporary tables go into a tempoary schema that is
automatically removed when you disconnect. What would more temporary
schemas acheive?
Temporary schemas are never removed (that I know of). They are reused,
and they trash the list of schemas in the middle with as many as they
were concurrently ever created. It seems there is a good reason for it,
according to the documentation, since it removes the overhead of having
to drop and then recreate it when needed on a new connection. The
problem is that they're all visible to everyone (although apparently
accessible only to admins and the schema's current user).
It is certainly not critical, and could be solved by the GUI admins by
simply avoiding showing the temp schemas, or provide an option to hide
them. But I wonder if it wouldn't make more sense to do it in the
database itself instead of each and every GUI admin. After all, who is
interested in which temp schemas have been created for internal use?
They're supposed to be of use exclusively to its owner and only during
the duration of the session. It would be much more sense that the temp
schema would be always called "pg_temp", and map it internally to the
real current temp schema, making all others hidden and inaccessible.
Cheers,
Ezequiel Tolnay
On Fri, Jul 22, 2005 at 12:41:25PM +1000, Ezequiel Tolnay wrote:
Tom Lane wrote:
Bruno Wolff III <bruno@wolff.to> writes:
What happens if there is more than one existing function with that name
already. Do all of the old functions get deleted?What happens if there are existing references to the function using the
old parameter list?The current approach is that if you want to change the parameter list or
result type, you have to issue DROP FUNCTION, with the resulting cascade
of any dependent objects. I think trying to change that would be a very
bad idea. It'd create a real mess in terms of consistency of views, and
what does it really buy you notationally over DROP/CREATE?What I see is that the overloading is a very cool feature, but is not
necessary in most cases, and it introduces unnecessary administration
hassles.
What are those unnecessary administration hassles? I'm not seeing what
could be so bad as to merit the added code complexity. Maybe the
problem is just that you are not using the appropiate administration
tool?
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)
# mail@etolnay.com.ar / 2005-07-22 12:41:25 +1000:
Tom Lane wrote:
Bruno Wolff III <bruno@wolff.to> writes:
What happens if there is more than one existing function with that name
already. Do all of the old functions get deleted?What happens if there are existing references to the function using the
old parameter list?The current approach is that if you want to change the parameter list or
result type, you have to issue DROP FUNCTION, with the resulting cascade
of any dependent objects. I think trying to change that would be a very
bad idea. It'd create a real mess in terms of consistency of views, and
what does it really buy you notationally over DROP/CREATE?What I see is that the overloading is a very cool feature, but is not
necessary in most cases, and it introduces unnecessary administration
hassles. If there would be an attribute stating if a procedure has been
created overlodable (enabled by default, for backwards-compabitility),
then CREATE OR REPLACE would always replace the current one, regardless
of the parameters, and DROP would not require any parameters at all, and
a creation of a function with the NOOVERLOAD attribute would not be
allowed if there is already a function marked for overloading.
What would your hassle-free CREATE OR REPLACE do in this situation?
What should the fiew look like after you replace foo() with
foo(int4)?
CREATE TYPE t1 AS (
a INTEGER,
b INTEGER,
c INTEGER
);
CREATE TYPE t1 AS (
a TEXT,
c TEXT
);
CREATE FUNCTION foo() RETURNS SETOF t1 ...
CREATE VIEW fiew AS SELECT a * b, c FROM SELECT foo();
CREATE OR REPLACE FUNCTION foo(INTEGER) RETURNS SETOF t2 ...
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote:
Thanks a lot, I've read the TODO and have now an idea of what is the
current trend or plans for future releases.Regarding my own needs, I'd be happy to "scratch my own itch", but
perhaps I should find some consensus before I engage myself in the task
of developing. And I would also need a little guidance in regards to how
to submit changes and all that.
http://www.postgresql.org/developer/coding should help you get started.
Also make sure to subscribe to -hackers and send an email there when
you find something on the TODO list (or not on the list) that you'd like
to work on.
* Scripting language: A very powerful feature in SQLServer is its
support of variables and control structures embedded in the SQL
language, thus allowing running complex scripts without the need to pack
them in a function and then running the function. This is especially
limiting in PG since it doesn't support the creation of temporary
functions. In MSSSQL, variables are prefixed with AT (@) symbols, must
be declared before they're used, live only within the prepared statement
and can be set using SET, SELECT and UPDATE. Also, the conditional
statements are only IF and WHILE, they support subqueries and function
calls in their expressions, and group statements using BEGIN and END. I
find prefixing variables thus is a lot less confusing and error prone
than the parameter alias and variables used in PL/pgSQL. For the
implementation on PG, I believe that perhaps it would be nice to use
curly brackets instead of BEGIN and END.
Well, psql will do some of what you want, but yes, it would be useful if
there was a means to run plpgsql code from the command line. There's
been some talk about this, but nothing definative afaik.
* Allow FETCH command to be used with CREATE TABLE tab AS qry (in place
of qry)
I'm not really clear on what you're looking for here..
* Allow a function's result type to be defined in the function itself,
similarly to the way OUT parameters are constructed. If a type for it
must be created for this purpose, set it as a system object and handle
its recreation automatically upon CREATE OR REPLACE FUNCTION to avoid
the current catch-22 dependency issues. Or perhaps let function
returning a SETOF RECORD to have an implicit (undefined) result type,
thus avoiding having to specify the type dynamically in the caller
statement SELECT func(...) AS (...)
Is there enough here for a TODO?
I hope a few of you will be interested in my proposals, so they get into
the TODO list. Once there, I'd be happy to contribute with some
development myself (either for these or other TODO items). I'm not a
proficient hacker with lots of time available, but I believe I'm a
fairly good programmer and I'm a Gentoo user so I have everything needed
to compile and build. But I believe I'll require a few months of
studying the code alone before I dare making any additions. So any hints
of how to become a PG developer to submit changes, and where to start
(or what documentation to read first) to have a fast head-start will be
highly appreciated.
From what I've seen, your best bet is to pick something (prefferably on
the TODO list) that looks fairly simple, send an email to -hackers
claiming it (and asking for any clarification, etc), and dive in.
If you're interested in internals or new feature development you should
absolutely subscribe to -hackers. Have I emphasised -hackers enough yet?
:)
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
Alvaro Herrera wrote:
Tom Lane wrote:
What I see is that the overloading is a very cool feature, but is not
necessary in most cases, and it introduces unnecessary administration
hassles.What are those unnecessary administration hassles? I'm not seeing what
could be so bad as to merit the added code complexity. Maybe the
problem is just that you are not using the appropiate administration
tool?
Functions are not the same as stored procedures, but since PG lacks
stored procedures, there is a necessity to use functions instead.
Stored procedures (in the RDBMSs that support them) are normally used to
1) run batch processes, and 2) separate the interface from the
database logic (by interface, I mean an interface with the client app).
Functions on the other side are very convenient as a kind of
parameterisable or extended view, and (if there are stored procedures
available) not really intended as an interface with the front-end.
One of the first hassles with using functions as an interface (mainly
with web-based) front-ends is that, despite de fact that you'd normally
never (or very rarely) use or require overloading, you're still required
to cast every parameter to match that of the function. And when you
have, say, 50 parameters (used by a web form to create, for instance, a
customer, with information spread around many tables), if you miss
casting the type on one of them (e.g. to int2 if that's the way it's
been defined) you get an error.
Another is that if you cannot use CREATE OR REPLACE to change one of the
parameters, thus having to drop and recreate, and this requires setting
all the permissions all over again.
Another one would be the impossibility to implement parameter defaults,
and the chance to provide some of the values in a different order than
that of the function's definition, by specifying the parameter names (a
feature already present in the TODO list) and letting the function use
the defaults for the missing parameters.
Anyway, this is not about being annoying, I think it would be great if
PG had not only its own special and unique features, but also all the
common features you would find in other RDBMSs, for portability and
convenience.
Cheers,
Ezequiel Tolnay
Roman Neuhauser wrote:
What would your hassle-free CREATE OR REPLACE do in this situation?
What should the fiew look like after you replace foo() with
foo(int4)?CREATE TYPE t1 AS (
a INTEGER,
b INTEGER,
c INTEGER
);
CREATE TYPE t1 AS (
a TEXT,
c TEXT
);CREATE FUNCTION foo() RETURNS SETOF t1 ...
CREATE VIEW fiew AS SELECT a * b, c FROM SELECT foo();
CREATE OR REPLACE FUNCTION foo(INTEGER) RETURNS SETOF t2 ...
In this case it would not allow the replace, since the function has both
type and interface dependencies and you're changing both the type and
the interface.
The idea is to use the NOOVERLOAD feature on functions that would be
used as an interface for the front-end, where the addition or
modification of a parameter or a result column is quite common, and
functions very rarely need or use overloading features.
Please have a look at my reply to the sister thread.
Cheers,
Ezequiel Tolnay
Jim C. Nasby wrote:
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote:
* Allow FETCH command to be used with CREATE TABLE tab AS qry (in place
of qry)I'm not really clear on what you're looking for here..
Fetching from a cursor should be equivalent to selecting from a table,
and a function could benefit from being able to run CREATE TABLE ... AS
using the results of a cursor.
Basically I've been trying to automate the handing of query results from
a web-front end, which cannot rely on temporary tables since the
sessions are short lived, but the results need to be retrieved in a
paged fashion. A great solution to this would be to have functions
opening a dynamic cursor and passing this cursor to another function
that would to the creation/maintenance of these transient tables, but
unfortunately there is no way of using a cursor from a function that
doesn't handle specifically its record type.
For this purpose (and perhaps many others) if figure it would be greate
if PG would support the command " CREATE TABLE tab AS FETCH ALL FROM cur
". It would be even better if in plpgsql you would be able to use a
cursor variable instead of an EXECUTE 'CREATE TABLE tab AS FETCH ALL
FROM "' || v_cur || '"'.
Cheers,
Ezequiel Tolnay
On Mon, Jul 25, 2005 at 11:35:14AM +1000, Ezequiel Tolnay wrote:
Functions are not the same as stored procedures, but since PG lacks
stored procedures, there is a necessity to use functions instead.
Ok, maybe I'm missing something, but the only difference between a
procedure and a function is that a function returns a value and a
procedure doesn't. There's no difference in capabilities so I don't
really understand the problem here.
Stored procedures (in the RDBMSs that support them) are normally used to
1) run batch processes, and 2) separate the interface from the
database logic (by interface, I mean an interface with the client app).
Functions on the other side are very convenient as a kind of
parameterisable or extended view, and (if there are stored procedures
available) not really intended as an interface with the front-end.
But AFAICS this is a distinction made by people (like in Pascal) but is
not a distinction at all. As far as the system is concerned they are
identical. Everything you can do in one of those "procedures" you can
do in PostgreSQL now, so what is the problem?
One of the first hassles with using functions as an interface (mainly
with web-based) front-ends is that, despite de fact that you'd normally
never (or very rarely) use or require overloading, you're still required
to cast every parameter to match that of the function. And when you
have, say, 50 parameters (used by a web form to create, for instance, a
customer, with information spread around many tables), if you miss
casting the type on one of them (e.g. to int2 if that's the way it's
been defined) you get an error.
Only if you're using an old version of postgres. Anyway, if you always
quote all your arguments this becomes a complete non-issue... In fact,
it will then complain if you have overloaded your function...
Another is that if you cannot use CREATE OR REPLACE to change one of the
parameters, thus having to drop and recreate, and this requires setting
all the permissions all over again.
Well, you have to do this anyway and any dependant objects and
currently cached query plans, etc would all have to be killed. This
would be solvable I guess, if there was a concensus to do it.
Another one would be the impossibility to implement parameter defaults,
and the chance to provide some of the values in a different order than
that of the function's definition, by specifying the parameter names (a
feature already present in the TODO list) and letting the function use
the defaults for the missing parameters.
These are on the todo list and have nothing to do with function
overloading AFAIK.
Anyway, this is not about being annoying, I think it would be great if
PG had not only its own special and unique features, but also all the
common features you would find in other RDBMSs, for portability and
convenience.
True, there is a tendancy to Oracle on things that aren't standardised.
I'm not sure how much of all this is supported in Oracle though.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
kleptog@svana.org (Martijn van Oosterhout) writes:
On Mon, Jul 25, 2005 at 11:35:14AM +1000, Ezequiel Tolnay wrote:
Functions are not the same as stored procedures, but since PG lacks
stored procedures, there is a necessity to use functions instead.Ok, maybe I'm missing something, but the only difference between a
procedure and a function is that a function returns a value and a
procedure doesn't. There's no difference in capabilities so I don't
really understand the problem here.
I'm in the same boat here. I don't grasp what is the *vital*
difference that makes a stored function, which differs from a stored
procedure in that it returns a value, so greatly different.
Stored procedures (in the RDBMSs that support them) are normally
used to 1) run batch processes, and 2) separate the interface from
the database logic (by interface, I mean an interface with the
client app). Functions on the other side are very convenient as a
kind of parameterisable or extended view, and (if there are stored
procedures available) not really intended as an interface with the
front-end.But AFAICS this is a distinction made by people (like in Pascal) but is
not a distinction at all. As far as the system is concerned they are
identical. Everything you can do in one of those "procedures" you can
do in PostgreSQL now, so what is the problem?
What may possibly be a difference that *would* be real is the ability
to manage transactions in a procedure.
That is, to periodically invoke "COMMIT"...
I would think that there was *some* value in having a language that
allowed invoking COMMIT as opposed to treating it as an ignorable
externality...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>
Even in Pascal a procedure and a function are the same except one
returns a result, the other does not.
And it C++ everything is a function, you just set the return type to
void for a proc.
Show quoted text
But AFAICS this is a distinction made by people (like in Pascal) but is
not a distinction at all. As far as the system is concerned they are
identical. Everything you can do in one of those "procedures" you can
do in PostgreSQL now, so what is the problem?
Chris Browne wrote:
kleptog@svana.org (Martijn van Oosterhout) writes:
On Mon, Jul 25, 2005 at 11:35:14AM +1000, Ezequiel Tolnay wrote:
Functions are not the same as stored procedures, but since PG lacks
stored procedures, there is a necessity to use functions instead.Ok, maybe I'm missing something, but the only difference between a
procedure and a function is that a function returns a value and a
procedure doesn't. There's no difference in capabilities so I don't
really understand the problem here.I'm in the same boat here. I don't grasp what is the *vital*
difference that makes a stored function, which differs from a stored
procedure in that it returns a value, so greatly different.
The difference is quite simple, but perhaps not evident if you don't
have much experience with other RDBMSs. A function is meant to return a
result (or a set of results) of a predefined type during execution,
whilst a stored procedure returns any number of results of arbitrary
types (as well as status messages and affected rows per operation)
"during" execution, just as what you would expect by running a script
asynchronously, fetching results as the script's processed. The
convenience of a stored procedure is in short that you can store a
script (procedure), assign a name to it, thus hiding its internals
conveniently from the interface layer. The interface, of course, on turn
must be capable of handling the various results returned, if any at all.
For instance, when running a procedure that you know will take a few
hours to process, you could return every minute or so a status message
to know what's going on. Or return in one go customer details plus
transactions plus a summary. Or launch in the background without waiting
for a results at all (which requires EXECUTE as opposed to SELECT). Or
to have a feedback of the rows affected in the various steps of the
procedure to finally receive a rowset with the results.
Cheers,
Ezequiel Tolnay
