Thread: 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/
> 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?
>
>                http://www.postgresql.org/docs/faq

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> 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.

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

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

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

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

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

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


>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

Ezequiel Tolnay wrote:
> (...) A function is meant to return a
> result (or a set of results) of a predefined type *during* execution,
> whilst a stored procedure (...)

I meant to say *after* instead of *during*. The capabilitie to return
results during execution could only be suported by stored procedures.

On Wed, Jul 27, 2005 at 12:45:12PM +1000, Ezequiel Tolnay wrote:
> Ezequiel Tolnay wrote:
> >(...) A function is meant to return a
> >result (or a set of results) of a predefined type *during* execution,
> >whilst a stored procedure (...)
>
> I meant to say *after* instead of *during*. The capabilitie to return
> results during execution could only be suported by stored procedures.

I know this is being fiddly but in PostgreSQL a function doesn't have
to return all its values in one go. It can, in a loop, do calculations,
return a row, do more calculations, return a row and these rows can be
received by the client in real time (ie not wait for completion).

But your description of stored procedures was useful. It's basically
more like a script you would feed to psql. You indicate that the
procedure can return multiple sets. How is this handled by the client?

If you're not returning data then it would be like a void function
using NOTICE to communicate with the client.

Still, thanks for the info...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> 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.

The client has to read the incoming data stream for indications of the
type of data that is coming next, then further read a description of
each field name, type, and nullabillity in the event it is a dataset.
It is not pretty.  I don't know how the higher level interfaces handle
it, but here is some interesting reading on TDS.  It has changed a bit
since 5.0, but the basics are the same.

http://www.sybase.com/content/1013412/tds34.pdf

Also, FreeTDS http://www.freetds.org is interesting.  They have high
level interfaces but I haven't had to use them.

- Ian


On 7/27/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Wed, Jul 27, 2005 at 12:45:12PM +1000, Ezequiel Tolnay wrote:
> > Ezequiel Tolnay wrote:
> > >(...) A function is meant to return a
> > >result (or a set of results) of a predefined type *during* execution,
> > >whilst a stored procedure (...)
> >
> > I meant to say *after* instead of *during*. The capabilitie to return
> > results during execution could only be suported by stored procedures.
>
> I know this is being fiddly but in PostgreSQL a function doesn't have
> to return all its values in one go. It can, in a loop, do calculations,
> return a row, do more calculations, return a row and these rows can be
> received by the client in real time (ie not wait for completion).
>
> But your description of stored procedures was useful. It's basically
> more like a script you would feed to psql. You indicate that the
> procedure can return multiple sets. How is this handled by the client?
>
> If you're not returning data then it would be like a void function
> using NOTICE to communicate with the client.
>
> Still, thanks for the info...
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > 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.
>
>
>