Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-30 Thread Vincenzo Romano 
Il 28 lug 2017 16:18, "Tom Lane"  ha scritto:

Vincenzo Romano  writes:
> I would like to understand the typo protection mentioned by Tom earlier:
> I need to understand the reason for creating that special case.

Well, case A:

create function foo(out x int4) returns setof int8 ...


Here the "setof record" would help against typos. The table() wouldn't.

This is indubitably a typo.

Case B:

create function foo(out x record) returns setof record ...


Here the table() would help. The setof record wouldn't.

I don't see any clear advantage beyond code style.

Now what?  Is the user expecting us to wrap x in an additional
layer of composite, or not?  Our current assumption is "not",
but it would be pretty inconsistent to do that if x did get
wrapped as long as it were any other type.


Moreover, there is another number of typos for which the syntax won't help.

There is a slight difference in the win cases because of an asymmetry
introduced to "avoid typos".

You can create more asymmetries in the syntax to make a construct more
general than others. The reasons would be, to me, quite disputable.


Yes, we could have resolved that ambiguity in one direction or the
other and then said that "returns record" or "returns setof record"
is OK regardless of the number of OUT parameters, but we didn't.


I was infact asking for the reasons.
I am still looking for an answer, if any.

Considering that the SQL-standard syntax for this is TABLE(), and that
hasn't got these issues in the first place, I don't feel any need to
revisit the question.


I don't think people chooses PgSQL because of its adherence to the
standard, but rather because of its smart deviances and enhancements.



regards, tom lane


Enzo.

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-28 Thread Tom Lane 
Vincenzo Romano  writes:
> I would like to understand the typo protection mentioned by Tom earlier:
> I need to understand the reason for creating that special case.

Well, case A:

create function foo(out x int4) returns setof int8 ...

This is indubitably a typo.

Case B:

create function foo(out x record) returns setof record ...

Now what?  Is the user expecting us to wrap x in an additional
layer of composite, or not?  Our current assumption is "not",
but it would be pretty inconsistent to do that if x did get
wrapped as long as it were any other type.

Yes, we could have resolved that ambiguity in one direction or the
other and then said that "returns record" or "returns setof record"
is OK regardless of the number of OUT parameters, but we didn't.
Considering that the SQL-standard syntax for this is TABLE(), and that
hasn't got these issues in the first place, I don't feel any need to
revisit the question.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-28 Thread Vincenzo Romano 
2017-07-28 8:36 GMT+02:00 David G. Johnston :
> On Thursday, July 27, 2017, David G. Johnston 
> wrote:
>>
>> On Thursday, July 27, 2017, Vincenzo Romano 
>> wrote:
>>>
>>> The main difference is that with RETURNS SETOF RECORD I still get the
>>> "usual"(tm) function argument list in the usual place: between two
>>> parentheses.
>>> It's a matter of style. And a consistent one.
>>> But I still don't get the point for not having it for a single column.
>>>
>>
>> Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can
>> be omitted. ". Sounds like you should you do just that.
>>
>
> Except you'd have no where to put the "setof" modifier...So, yeah, you
> probably aren't going to personal style preference catered to here.
>
> David J.

Thanks David.

I have found the actual documentation of the inconsistent syntax.

It's here
https://www.postgresql.org/docs/9.6/static/sql-createfunction.html
at the description for the parameter "rettype":

[QUOTE]
When there are OUT or INOUT parameters, the RETURNS clause can be omitted.
If present, it must agree with the result type implied by the output parameters:
RECORD if there are **multiple output parameters**, or the same type
as the single output parameter.
The SETOF modifier indicates that the function will return a set of
items, rather than a single item.
The type of a column is referenced by writing table_name.column_name%TYPE.
[/QUOTE]

That single predicate, "multiple output parameters", is creating the
(useless?) special case for a single column output.
I would like to understand the typo protection mentioned by Tom earlier:
I need to understand the reason for creating that special case.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-28 Thread David G. Johnston 
On Thursday, July 27, 2017, David G. Johnston 
wrote:

> On Thursday, July 27, 2017, Vincenzo Romano  > wrote:
>>
>> The main difference is that with RETURNS SETOF RECORD I still get the
>> "usual"(tm) function argument list in the usual place: between two
>> parentheses.
>> It's a matter of style. And a consistent one.
>> But I still don't get the point for not having it for a single column.
>>
>>
> Docs say: " When there are OUT or INOUT parameters, the RETURNS clause
> can be omitted. ". Sounds like you should you do just that.
>
>
Except you'd have no where to put the "setof" modifier...So, yeah, you
probably aren't going to personal style preference catered to here.

David J.

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-28 Thread David G. Johnston 
On Thursday, July 27, 2017, Vincenzo Romano 
wrote:
>
> The main difference is that with RETURNS SETOF RECORD I still get the
> "usual"(tm) function argument list in the usual place: between two
> parentheses.
> It's a matter of style. And a consistent one.
> But I still don't get the point for not having it for a single column.
>
>
Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can
be omitted. ". Sounds like you should you do just that.

David J.

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-27 Thread Vincenzo Romano 
2017-07-27 16:03 GMT+02:00 Tom Lane :

Thanks a lot for your reply with valuable details.

> Vincenzo Romano  writes:
>> CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT )
>> RETURNS SETOF RECORD
>
>> The error message for afun1() reads:
>> ERROR:  function result type must be text because of OUT parameters
>
> Indeed.  It's trying to protect you from simple typos.

Which typo?
It looks it's preventing me from consistently using another simple
syntax for FRSes.

CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT )
RETURNS SETOF TEXT ...

CREATE OR REPLACE FUNCTION afun2( OUT ot TEXT, OUT it INT )
RETURNS SETOF RECORD ...

CREATE OR REPLACE FUNCTION afun3( OUT ot TEXT, OUT it INT, OUT on NUMERIC )
RETURNS SETOF RECORD ...

CREATE OR REPLACE FUNCTION afun4( OUT ot TEXT, OUT it INT, OUT on
NUMERIC, OUT od DATE )
RETURNS SETOF RECORD ...

This means that RETURNS SETOF RECORD syntax is only available from 2 columns on.
While RETURNS TABLE() syntax is always available, *also* for 1 column.
I thought that "RETURNS SETOF RECORD" was meant for FRSes just like
RETURN TABLE() is.
Why not, then?

> If you want a consistent syntax I'd suggest
>
> CREATE OR REPLACE FUNCTION afun1() RETURNS TABLE (ot text) ...

Indeed.
The main difference is that with RETURNS SETOF RECORD I still get the
"usual"(tm) function argument list in the usual place: between two
parentheses.
It's a matter of style. And a consistent one.
But I still don't get the point for not having it for a single column.
Surely it's my fault.

> It's still really "setof text" under the hood.
>
> regards, tom lane

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-27 Thread Tom Lane 
Vincenzo Romano  writes:
> CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT )
> RETURNS SETOF RECORD

> The error message for afun1() reads:
> ERROR:  function result type must be text because of OUT parameters

Indeed.  It's trying to protect you from simple typos.

If you want a consistent syntax I'd suggest

CREATE OR REPLACE FUNCTION afun1() RETURNS TABLE (ot text) ...

It's still really "setof text" under the hood.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general