Thread: Where are we on stored procedures?

Gavin and Neil made some noise in late September about implementing
stored procedures for PG 8.1, but I haven't heard anything more about
it since that thread died off.  I've been getting some pressure inside
Red Hat to see us support more of the JDBC CallableProcedure spec, so
I'd like to reopen the discussion.

In the previous discussion starting here:
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php
it seemed that we were bandying around several different issues.
People wanted "procedures" to differ from our current implementation
of "functions" in such ways as:

1. Executing outside the database engine, and therefore being able to
start/commit transactions.  (This is *fundamentally* different from our
current concept of functions, and I think that any implementation that
tries to gloss over the difference will be doomed to failure.)

2. Having OUT parameters, and perhaps also INOUT parameters.  At least
in the JDBC spec, these are seen as scalar values, and so the feature
is really just syntactic sugar for returning a composite type or row
value.  For instance, a procedure foo(x IN int, y OUT text, z OUT float)
could perhaps be called via
    SELECT y, z FROM foo(42);
where foo(x) is seen as returning the rowtype (y text, z float).

3. Being able to return multiple result sets, ie, more than one SETOF
something.  In our previous discussion we tied this to OUT parameters
but they're not necessarily the same thing --- the JDBC spec sees result
sets as totally different objects.

4. Not having a distinguished function result, a/k/a returning void.
While a function result is unnecessary given OUT parameters, this feels
like an accidental thing rather than an essential aspect.

5. Matching parameters by name instead of by position.

6. Allowing parameters to be omitted from a call, with default values
used instead.

#5 and #6 would also be interesting for regular functions, but it's
unclear how well we can support them without disallowing overloading
of procedure/function names --- which of course is a nonstarter for
the existing function facility.

(Gavin and Neil's first proposal also involved inventing a concept of
variables at the SQL level, primarily so that there would be something
to receive the results of OUT parameters.  I found that distasteful and
would prefer to avoid it.  Another thing that came up was allowing a
procedure -- in one or more of these senses -- to be used as a trigger,
but I think that's a red herring.  None of the above attributes are
particularly relevant to a trigger.)

On looking at this list, it seems to me that #1 (transactionality) is
largely orthogonal to the other points, which all have to do with
passing and returning values.  The main reason why we might consider
all these things together is that no one is very excited about the idea
of having even more than two kinds of objects, so there is a lot of
temptation to press for having all these features in "procedures"
rather than recognize that they are largely separable needs.

The more I think about it, the more I think that #1 belongs outside the
database entirely.  The database engine more or less has to operate
entirely within transactions --- heck, we cannot even look up a stored
procedure's definition in a system catalog without starting a
transaction.  This is not to say that the facility can't exist
physically within the backend, but that it would work a lot better if
it weren't logically inside the database.  What about inventing a
protocol facility that lets clients send a chunk of, say, Perl or
Python code to execute in an interpreter that can in turn send commands
to the DB engine proper?  The point here is that that interpreter is
wrapped around the DB engine, not vice versa as occurs when executing a
plperl or plpython function.  (The only real difference between this
idea and just executing the same code on the client side is avoiding
network round trips.)

BTW, using plpgsql in this fashion is a nonstarter, at least with
anything resembling its current implementation.  Because plpgsql relies
on the database engine to do even simple expression evaluation, it's
just hopeless to think of it doing anything useful outside a
transaction.  But we have plenty of external programming languages
available that are perfectly capable of doing their own arithmetic and
logic, and so could meaningfully drive the database engine through a
series of transactions.

Having said all that, I don't have any personal interest in pursuing #1
immediately.  (Though anyone who does is welcome to.)  What I would
like to see is some forward movement on the other points, particularly
#2 which is blocking my Red Hat coworkers from making progress.  So the
real bottom line to this overly long email is that I'd like to create
a consensus that it's OK to work on #2 and perhaps #3 in the context of
our existing function facility, without tackling #1.  This'd involve
work in both the server and the JDBC driver.

Comments?

            regards, tom lane

 --- Tom Lane <tgl@sss.pgh.pa.us> escreveu:
> Gavin and Neil made some noise in late September about implementing
> stored procedures for PG 8.1, but I haven't heard anything more about
> it since that thread died off.  I've been getting some pressure inside
> Red Hat to see us support more of the JDBC CallableProcedure spec, so
> I'd like to reopen the discussion.
>


I don't know if I can talk about it here, but here I go. Please let me know if
it is not appropriate.

> In the previous discussion starting here:
> http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php
> it seemed that we were bandying around several different issues.
> People wanted "procedures" to differ from our current implementation
> of "functions" in such ways as:
>
>
> 2. Having OUT parameters, and perhaps also INOUT parameters.  At least
> in the JDBC spec, these are seen as scalar values, and so the feature
> is really just syntactic sugar for returning a composite type or row
> value.  For instance, a procedure foo(x IN int, y OUT text, z OUT float)
> could perhaps be called via
>     SELECT y, z FROM foo(42);
> where foo(x) is seen as returning the rowtype (y text, z float).
>

That would be very good.


Could I add another item?

Could we have the row count of statements executed inside a procedure/function
returned to client?

I know Tom Lane, that you already talked about that this could be unnecessary,
but we from Npgsql sometimes get reports about the support of needing to know
the number of rows affected by a function/procedure call.

You told me about the get diagnostics ROWCOUNT and it worked like a charm, but
I was thinking about something without the need to change the procedure, as
even this modification needs some aware of client to be able to get the result.

Please, jdbc guys, if you know some easy way of doing this, please let me know
so I can implement in Npgsql.



Thanks in advance.

Regards,

Francisco Figueiredo Jr.


__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/

"Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> writes:
> Could I add another item?

> Could we have the row count of statements executed inside a
> procedure/function returned to client?

IMHO that request is completely bogus; if the procedure wants to tell
the client that, it's the procedure's responsibility to return the
number as a result.  Doing what you ask (a) would arguably be a security
violation, and (b) the info would be impossible for the client to
interpret in any but the most trivial cases anyway.  Consider for
instance a procedure that contains different queries in the THEN and
the ELSE arms of an IF, or contains loops executed a variable number
of times.  Without access to the results of the control flow tests, the
client could not know which number means what.

            regards, tom lane

Hi Tom,

On Thu, 24 Feb 2005, Tom Lane wrote:

> Gavin and Neil made some noise in late September about implementing
> stored procedures for PG 8.1, but I haven't heard anything more about
> it since that thread died off.  I've been getting some pressure inside
> Red Hat to see us support more of the JDBC CallableProcedure spec, so
> I'd like to reopen the discussion.
>
> In the previous discussion starting here:
> http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php
> it seemed that we were bandying around several different issues.
> People wanted "procedures" to differ from our current implementation
> of "functions" in such ways as:
>
> 1. Executing outside the database engine, and therefore being able to
> start/commit transactions.  (This is *fundamentally* different from our
> current concept of functions, and I think that any implementation that
> tries to gloss over the difference will be doomed to failure.)

We have some ideas on this but haven't made in progress in terms of code.
The main reason that Josh and others were arguing strongly for transaction
level control within SPs was that they wanted to be able to VACUUM,
CLUSTER, etc from within an SP.

>
> 2. Having OUT parameters, and perhaps also INOUT parameters.  At least
> in the JDBC spec, these are seen as scalar values, and so the feature
> is really just syntactic sugar for returning a composite type or row
> value.  For instance, a procedure foo(x IN int, y OUT text, z OUT float)
> could perhaps be called via
>     SELECT y, z FROM foo(42);
> where foo(x) is seen as returning the rowtype (y text, z float).

Again, ideas and design but not too much code. We got the parameter modes
into the grammar, stored them in a new system catalog, and some other
minor stuff.

The composite type stuff is precisely what the JDBC driver does at the
moment. I guess the feeling is that for those used to SPs in other
databases, having to use composite types is a bit of a hack.

>
> 3. Being able to return multiple result sets, ie, more than one SETOF
> something.  In our previous discussion we tied this to OUT parameters
> but they're not necessarily the same thing --- the JDBC spec sees result
> sets as totally different objects.
>
> 4. Not having a distinguished function result, a/k/a returning void.
> While a function result is unnecessary given OUT parameters, this feels
> like an accidental thing rather than an essential aspect.
>
> 5. Matching parameters by name instead of by position.
>

This is kind of independent of SPs, I have some code which implements it
but I need to bring it up to HEAD.

> 6. Allowing parameters to be omitted from a call, with default values
> used instead.

Some code on this too.

>
> #5 and #6 would also be interesting for regular functions, but it's
> unclear how well we can support them without disallowing overloading
> of procedure/function names --- which of course is a nonstarter for
> the existing function facility.

We can support 5 for functions but not 6.

>
> (Gavin and Neil's first proposal also involved inventing a concept of
> variables at the SQL level, primarily so that there would be something
> to receive the results of OUT parameters.  I found that distasteful and
> would prefer to avoid it.  Another thing that came up was allowing a
> procedure -- in one or more of these senses -- to be used as a trigger,
> but I think that's a red herring.  None of the above attributes are
> particularly relevant to a trigger.)

I'd like to hear what you had in mind for OUT parameters. Oracle and IBM
have host variables which is more or less what we were looking at but SQL
server just returns a result set as if it were a function. Strangely
enough, it can return multiple result sets with different descriptors (ie,
different row types).

I think there is some merit in supporting procedures with triggers but I'm
not sure its necessary for a first attempt.

>
> On looking at this list, it seems to me that #1 (transactionality) is
> largely orthogonal to the other points, which all have to do with
> passing and returning values.  The main reason why we might consider
> all these things together is that no one is very excited about the idea
> of having even more than two kinds of objects, so there is a lot of
> temptation to press for having all these features in "procedures"
> rather than recognize that they are largely separable needs.
>
> The more I think about it, the more I think that #1 belongs outside the
> database entirely.  The database engine more or less has to operate
> entirely within transactions --- heck, we cannot even look up a stored
> procedure's definition in a system catalog without starting a
> transaction.  This is not to say that the facility can't exist
> physically within the backend, but that it would work a lot better if
> it weren't logically inside the database.  What about inventing a
> protocol facility that lets clients send a chunk of, say, Perl or
> Python code to execute in an interpreter that can in turn send commands
> to the DB engine proper?  The point here is that that interpreter is
> wrapped around the DB engine, not vice versa as occurs when executing a
> plperl or plpython function.  (The only real difference between this
> idea and just executing the same code on the client side is avoiding
> network round trips.)
>
> BTW, using plpgsql in this fashion is a nonstarter, at least with
> anything resembling its current implementation.  Because plpgsql relies
> on the database engine to do even simple expression evaluation, it's
> just hopeless to think of it doing anything useful outside a
> transaction.  But we have plenty of external programming languages
> available that are perfectly capable of doing their own arithmetic and
> logic, and so could meaningfully drive the database engine through a
> series of transactions.

This is one of the real tough issues Neil and I were trying to work out. I
seem to remember you noting that without transaction control, SPs were
irrelevant :-).

> Having said all that, I don't have any personal interest in pursuing #1
> immediately.  (Though anyone who does is welcome to.)  What I would
> like to see is some forward movement on the other points, particularly
> #2 which is blocking my Red Hat coworkers from making progress.  So the
> real bottom line to this overly long email is that I'd like to create
> a consensus that it's OK to work on #2 and perhaps #3 in the context of
> our existing function facility, without tackling #1.  This'd involve
> work in both the server and the JDBC driver.
>
> Comments?

Well, Neil and I have at least looked over all the issues in a fair degree
of detail and made some plans. We've done the low hanging fruit: new
system catalog, CREATE PROCEDURE/DROP PROCEDURE, named notation (as
opposed to positional notation) for parameters at routine invocation
time, and a bit of prototyping. We were looking to finish this around
about now but we've been asked by Fujitsu to focus full time on Slony2,
for which we are working on prototypes.

As you've seen internally at Red Hat, the OUT parameter stuff is a real
show stopper for the JDBC guys. It would be good to see this in 8.1 but
I'm not sure when either Neil or I will find some time to look at it.

Thanks,

Gavin

Gavin Sherry <swm@linuxworld.com.au> writes:
> On Thu, 24 Feb 2005, Tom Lane wrote:
>> For instance, a procedure foo(x IN int, y OUT text, z OUT float)
>> could perhaps be called via
>> SELECT y, z FROM foo(42);
>> where foo(x) is seen as returning the rowtype (y text, z float).

> The composite type stuff is precisely what the JDBC driver does at the
> moment. I guess the feeling is that for those used to SPs in other
> databases, having to use composite types is a bit of a hack.

Yeah, but only because you have to do it explicitly.  I was wondering
whether we couldn't bury that mechanism under the hood.  (In particular,
given the improved support in 8.0 for anonymous record types, we could
in theory have the backend invent a record type on-the-fly to match
whatever list of OUT parameters a particular function has.)

>> (Gavin and Neil's first proposal also involved inventing a concept of
>> variables at the SQL level, primarily so that there would be something
>> to receive the results of OUT parameters.  I found that distasteful and
>> would prefer to avoid it.

> I'd like to hear what you had in mind for OUT parameters.

Essentially I'm thinking about the JDBC solution, but automated a bit
better.  You do
    SELECT * FROM function(a,b,c);
where only the IN (including INOUT) parameters are listed in the call,
and the OUT (including INOUT) parameters make up a result record type
that doesn't need to be explicitly declared.  I don't know yet what's
the cleanest way to handle this in terms of what appears in pg_proc.

If you insist we can allow "SELECT * FROM" to be spelled "CALL" or
some such, but I think I'd prefer to keep that notation in reserve
for "real" stored procedures.

This doesn't address the question of SETOF results, of course.  I'm
leaning towards returning those as cursors.

> This is one of the real tough issues Neil and I were trying to work out. I
> seem to remember you noting that without transaction control, SPs were
> irrelevant :-).

Well, I think that when people ask us for "stored procedures", most of
them mean that they want transaction control.  But the JDBC issues that
my Red Hat compatriots are currently worried about just have to do with
OUT parameters in a CallableStatement, so I'd like to make sure we solve
that in 8.1 regardless of whether anyone makes any progress on
outside-of-transactions stored procedures.

> As you've seen internally at Red Hat, the OUT parameter stuff is a real
> show stopper for the JDBC guys. It would be good to see this in 8.1 but
> I'm not sure when either Neil or I will find some time to look at it.

Rats, I was hoping you'd answer you had it about done ;-).  But if you
can pass over what you have, I'd like to see about pressing forward.

            regards, tom lane

Tom Lane wrote:
> Essentially I'm thinking about the JDBC solution, but automated a bit
> better.

So would your proposal invent a new "stored procedure" construct, or
just add some sugar to the existing function stuff? i.e. will you be
able to issue a CREATE FUNCTION that specifies OUT parameters?

> This doesn't address the question of SETOF results, of course.  I'm
> leaning towards returning those as cursors.

This is part of the reason I liked the approach of introduced SQL-level
variables. Besides being a feature that has some use in itself, it could
be extended reasonably cleanly to allow (effectively) SETOF variables
and rowtype variables.

> Well, I think that when people ask us for "stored procedures", most of
> them mean that they want transaction control.

Yes, that is certainly what Gavin and I spent most of our time banging
our heads against the wall on :(

> But if you can pass over what you have, I'd like to see about
> pressing forward.

Sure, I've attached a very WIP patch with the utility command
definitions; unfortunately I don't think it will be of much use, as much
of it is CREATE PROCEDURE-related boilerplate. Gavin will update the
matching-arguments-by-name code to HEAD at some point in the future; I
believe that works fine for functions (since we just error out in case
of ambiguity), so we can include it in 8.1 independently on any other
work on SPs.

-Neil

> 1. Executing outside the database engine, and therefore being able to
> start/commit transactions.  (This is *fundamentally* different from
our
> current concept of functions, and I think that any implementation that
> tries to gloss over the difference will be doomed to failure.)

Back in the early days of nested transactions, you could begin/commit
while within a transaction...they could be pushed and popped off of a
stack.

Supposing you could do that once again, would there be any reason why a
SP should be extra-transactional?

Merlin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
| "Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> writes:
|
|>Could I add another item?
|
|
|>Could we have the row count of statements executed inside a
|>procedure/function returned to client?
|
|
| IMHO that request is completely bogus; if the procedure wants to tell
| the client that, it's the procedure's responsibility to return the
| number as a result.  Doing what you ask (a) would arguably be a security
| violation, and (b) the info would be impossible for the client to
| interpret in any but the most trivial cases anyway.  Consider for
| instance a procedure that contains different queries in the THEN and
| the ELSE arms of an IF, or contains loops executed a variable number
| of times.  Without access to the results of the control flow tests, the
| client could not know which number means what.

Hi Tom Lane.

Thanks for feedback.

In fact, I think people keep requesting me support on Npgsql for that
because MS Sql server supports it and they are porting their code to use
Postgresql and facing that difficult.

Indeed, for (a) we could use an approach similar to Ms sql server. We
could have a way of the procedure say if it wanted to send the rows
affected information or not.
And about (b) I think that it is only on trivial cases that people
really want to use this feature :)


But thinking on other side I think this in only a differ in backend's
idiom. I already answered your solution of get diagnostics and I will
provide a litle FAQ about it so people can see how it is done on postgresql.

Regards,

Francisco Figueiredo Jr.


- --
Regards,

Francisco Figueiredo Jr.
Membro Fundador do Projeto MonoBrasil - MonoBrasil Project Founder Member
http://monobrasil.softwarelivre.org



- -------------
"Science without religion is lame;
religion without science is blind."

~                  ~ Albert Einstein
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iQEVAwUBQh854P7iFmsNzeXfAQJ7UQf+Jqd4G7EE7YFyDLRBsuoGLZTYl7N/ZY0M
yeIgRbTNw3HPeHb+OiAI+MMXCWxu0YU4OgLOvqkls9NKHkeuMjdSwmsCfwPL0qfc
sZT6UqQJyap6skH1VJ7jQEhfgjesfpqYzUoyeJUJDiRyFsEbM4sbdnDdpMawf/jq
jgzx50lpgQtFOblsaMlfklLiUZa47FeMC8M64XcmzE+IIdfJcx0kWYQ+LKtoimNs
9SH7Z1v3lnNKvucLSw5eMT/y2Ldu8Qgr4/eK7JroKcI4JbHOlNtirrovAoinBjwB
Otcwtd2tRqffFCrF6MqsSzuXn+S2C4Wj9/0qWZ1CIzevigb+QfXOIw==
=h32q
-----END PGP SIGNATURE-----

Neil Conway <neilc@samurai.com> writes:
> Tom Lane wrote:
>> Essentially I'm thinking about the JDBC solution, but automated a bit
>> better.

> So would your proposal invent a new "stored procedure" construct, or
> just add some sugar to the existing function stuff? i.e. will you be
> able to issue a CREATE FUNCTION that specifies OUT parameters?

I certainly intend to be able to say OUT in CREATE FUNCTION.  I'm not
sure what you consider to be "a new construct".  I'm not thinking of
making a new system catalog, for instance, just new column(s) in
pg_proc.

>> This doesn't address the question of SETOF results, of course.  I'm
>> leaning towards returning those as cursors.

> This is part of the reason I liked the approach of introduced SQL-level
> variables. Besides being a feature that has some use in itself, it could
> be extended reasonably cleanly to allow (effectively) SETOF variables
> and rowtype variables.

AFAICS that would force every SETOF result to be materialized, which
would be a bit of an efficiency hit.  The main reason I don't like it,
though, is that variables in a language that hasn't got control
structures are fundamentally wrong.  (And no, I'm not interested in
adding IF and LOOP later ;-)) They could only be useful as an
intermediate step in pushing data from the server to the client, so
why invent all the extra concept and mechanism instead of just pushing
the data immediately?

Basically what I am thinking is that we have all the infrastructure
today to solve the OUT-parameter problem, it's just not wrapped up in
an easy-to-use package.

>> But if you can pass over what you have, I'd like to see about
>> pressing forward.

> Sure, I've attached a very WIP patch with the utility command
> definitions; unfortunately I don't think it will be of much use, as much
> of it is CREATE PROCEDURE-related boilerplate.

Thanks.

> Gavin will update the
> matching-arguments-by-name code to HEAD at some point in the future; I
> believe that works fine for functions (since we just error out in case
> of ambiguity), so we can include it in 8.1 independently on any other
> work on SPs.

Agreed, that should be mostly orthogonal.

            regards, tom lane

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
>> 1. Executing outside the database engine, and therefore being able to
>> start/commit transactions.  (This is *fundamentally* different from our
>> current concept of functions, and I think that any implementation that
>> tries to gloss over the difference will be doomed to failure.)

> Back in the early days of nested transactions, you could begin/commit
> while within a transaction...they could be pushed and popped off of a
> stack.

Those weren't actual transaction starts or commits though; they were
(in our current terminology) savepoints.  People want *real* commits
in stored procedures, not fakes.  For instance consider an SP that
wants to issue a series of VACUUM commands --- if there's a hidden
transaction block around the SP, this must fail.  Locking considerations
may force use of real commits and real rollbacks, too.  You can't
release a lock by committing a subtransaction.
        regards, tom lane

Markus Schaber <schabios@logi-track.com> writes:
> Tom Lane schrieb:
>> given the improved support in 8.0 for anonymous record types, we could
>> in theory have the backend invent a record type on-the-fly to match
>> whatever list of OUT parameters a particular function has.)

> It would not be necessarily on the fly, at least in the first step we
> possibly get away with declaraing the returned tuples at creation time
> and implicitly creating those tuple types. The declaration could be like
> "returns (touchedrows int, somethingelse datetime), setof (article int,
> description text)" for a function/method that has two resultsets, one of
> those with always one row.

The advantage of not explicitly creating the rowtypes is that we don't
need to worry about choosing nonconflicting names for them.  So I think
I'd go down the anonymous-rowtype path even in the first cut.

            regards, tom lane

Hi, Francisco,

Francisco Figueiredo Jr. schrieb:

> In fact, I think people keep requesting me support on Npgsql for that
> because MS Sql server supports it and they are porting their code to use
> Postgresql and facing that difficult.
>
> Indeed, for (a) we could use an approach similar to Ms sql server. We
> could have a way of the procedure say if it wanted to send the rows
> affected information or not.
> And about (b) I think that it is only on trivial cases that people
> really want to use this feature :)

As you need to tweak the function/procedure source to implement (a), you
can also tweak the function/procedure to return the row cound and
whatever other diagnostics you need.

For newly implemented stored procedures, we could create a special
diagnostics result set that every procedure creates. This usually
contains some success/error information, but the procedure could add
additional rows to it.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Hi, Tom,

Tom Lane schrieb:

> Yeah, but only because you have to do it explicitly.  I was wondering
> whether we couldn't bury that mechanism under the hood.  (In particular,
> given the improved support in 8.0 for anonymous record types, we could
> in theory have the backend invent a record type on-the-fly to match
> whatever list of OUT parameters a particular function has.)

It would not be necessarily on the fly, at least in the first step we
possibly get away with declaraing the returned tuples at creation time
and implicitly creating those tuple types. The declaration could be like
"returns (touchedrows int, somethingelse datetime), setof (article int,
description text)" for a function/method that has two resultsets, one of
those with always one row.

markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Hi, Tom,

Tom Lane schrieb:

> Yeah, but only because you have to do it explicitly.  I was wondering
> whether we couldn't bury that mechanism under the hood.  (In particular,
> given the improved support in 8.0 for anonymous record types, we could
> in theory have the backend invent a record type on-the-fly to match
> whatever list of OUT parameters a particular function has.)

It would not be necessarily on the fly, at least in the first step we
possibly get away with declaraing the returned tuples at creation time
and implicitly creating those tuple types. The declaration could be like
"returns (touchedrows int, somethingelse datetime), setof (article int,
description text)" for a function/method that has two resultsets, one of
those with always one row.

markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Markus Schaber wrote:
| Hi, Francisco,
|
| Francisco Figueiredo Jr. schrieb:
|
|
|>In fact, I think people keep requesting me support on Npgsql for that
|>because MS Sql server supports it and they are porting their code to use
|>Postgresql and facing that difficult.
|>
|>Indeed, for (a) we could use an approach similar to Ms sql server. We
|>could have a way of the procedure say if it wanted to send the rows
|>affected information or not.
|>And about (b) I think that it is only on trivial cases that people
|>really want to use this feature :)
|
|
| As you need to tweak the function/procedure source to implement (a), you
| can also tweak the function/procedure to return the row cound and
| whatever other diagnostics you need.

Yeap, You are right.
|
| For newly implemented stored procedures, we could create a special
| diagnostics result set that every procedure creates. This usually
| contains some success/error information, but the procedure could add
| additional rows to it.
|


I think this could be a very good idea. I think you could create an
special tag which would say to create this special resultset or not as,
I think, not every stored procedure would need it.

I think this would be very nice.

Thanks Markus, for feedback.



- --
Regards,

Francisco Figueiredo Jr.
Membro Fundador do Projeto MonoBrasil - MonoBrasil Project Founder Member
http://monobrasil.softwarelivre.org



- -------------
"Science without religion is lame;
religion without science is blind."

~                  ~ Albert Einstein
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iQEVAwUBQiPPOf7iFmsNzeXfAQLFYQgAkiBLkYi8lKQ6NNJ2qP48nH8PmODoe4Wp
FPB7lNvo9fLw0aw5rbztcu19FtvcJBJuFBFPc2LKImpszuJ2hcD02pTGqx3UxMR8
Yz3edTijCziHg8uBTiXQV0vHZ5WeE2/sEp+ve5heanDdzAcwLiCfDPxTR1XfVYaP
AJfLIHspwVkrXcEbtgwEdPX91QG/cLYjcRR/fhiaH4s8I3Hi3o9ZitQ4bkdOdosw
10+TMrgzbtXKdEOhWu9xgvdcujksjKi1xXXXSwr+L5WPi4y9iohnIi6X9j9wWlOw
fr9p2lYJgfzpq71Cl8dRlW+d0pRJFUlHiNaT/Adhzwsozc459Vix8Q==
=Jcuy
-----END PGP SIGNATURE-----