2012/9/24 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:
> Having read through this thread, the consensus seems to be that we don't
> want this patch as it is (and I agree with that).
> As I understand it, you are trying to solve two problems:
> 1. Passing parameters to a DO statement. You could quote the parameters and
> inline them in the block itself in the client, but quoting can be difficult
> to do correctly and is not as readable anyway, so I agree it would be good
> to be able to pass them separately.
> 2. Returning results from a DO statement. At the moment, a DO statement is a
> utility statement, so it cannot return a result set.
> Two different approaches to these problems have been discussed that have
> some support:
> A) WITH FUNCTION syntax, to allow defining a "temporary function" for a
> single query, similar to the current WITH syntax.
> B) DO ... USING <parameter list> syntax. This is a straightforward extension
> of the current DO syntax, just adding a parameter list to it. Not sure how
> to return a result set from this, perhaps also support a RETURNS keyword,
> similar to CREATE FUNCTION.
> I'm ok with either of those approaches. A) would be more flexible, while B)
> would be straightforward extension of what we already have.
> I'm marking this patch as rejected in the commitfest app. Please pursue the
> WITH FUNCTION or DO ... USING syntax instead.
A basic discussion should be about a character of DO statements. What
it should be? Temporary function or temporary stored procedure. Both
variants are correct. I prefer idea, where DO is temporary procedure.
A reply on this question solves a question about returning result from
DO statement. We can support more calling context for DO statements
like we do with general functions - and then both your @A and @B
variants should be possible supported.
A blocker for @B is unsupported parametrization for utility
statements. If we can support a CALL statement, then we will have
solution for parametrized DO statement too.
From my perspective, missing parametrized DO is not blocker for
anything. Is not super elegant, but workaround needs only a few lines
more. \gsets solves last missing functionality.
So I would to close this topic - we should to implement procedures and
CALL statement first - this functionality is relative clean - a base
is described by ANSI/SQL - and as next step we can reimplement
anynymous (temporary) functions or procedures.
> On 31.08.2012 21:27, Pavel Stehule wrote:
>> 2012/8/31 Dimitri Fontaine<dimitri(at)2ndquadrant(dot)fr>:
>>> Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com> writes:
>>>>> Pavel, you didn't say what you think about the WITH FUNCTION proposal?
>>>> I don't like it - this proposal is too "lispish" - it is not SQL
>>> We're not doing lambda here, only extending a facility that we rely on
>>> today. The function would be named, for one. I don't know what you mean
>>> by SQL being lispish here, and I can't imagine why it would be something
>>> to avoid.
>>>>> And you didn't say how do you want to turn a utility statement into
>>>>> something that is able to return a result set.
>>>> if we support "real" procedures ala sybase procedures (MySQL, MSSQL..)
>>>> - then we can return result with same mechanism - there are no
>>>> significant difference between DO and CALL statements - you don't know
>>>> what will be result type before you call it.
>>> Currently we don't have CALL, and we have DO which is not a query but a
>>> utility statement. Are you proposing to implement CALL? What would be
>>> the difference between making DO a query and having CALL?
>> defacto a CALL statement implementation can solve this issue.
>> The core of this issue is an impossibility using parameters for
>> utility statements. CALL and DO are utility statements - and if we
>> can use parameters for CALL, then we can do it for DO too.
>> CALL statement starts a predefined batch - inside this batch, you can
>> do anything - can use COMMIT, ROLLBACK, SELECTs, ... DO is some batch
>> with immediate start. Sure, there is relative significant between
>> stored procedures implemented in popular RDBMS and although I don't
>> like T-SQL too much, I like sybase concept of stored procedures - it
>> is strong and very useful for maintaining tasks.
>>> Dimitri Fontaine
>>> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> - Heikki
In response to
pgsql-hackers by date
|Next:||From: Pavel Stehule||Date: 2012-09-27 19:09:19|
|Subject: Re: proposal - assign result of query to psql variable|
|Previous:||From: Magnus Hagander||Date: 2012-09-27 18:34:16|
|Subject: Re: Oid registry|