Re: Is a function to a 1-component record type undeclarable?

po 22. 11. 2021 v 18:43 odesílatel Chapman Flack <chap(at)anastigmatix(dot)net>
napsal:

> On 11/22/21 11:59, Pavel Stehule wrote:
> > And if we allow RETURNS RECORD, then there will be new inconsistency
> > between OUT variables and RETURNS TABLE
>
> I don't really see it as a new inconsistency, so much as the same old
> inconsistency but with an escape hatch if you really mean the other thing.
>
> I take the consistency you speak of here to be "anything you can say with
> OUT parameters is equivalent to something you can say with RETURNS TABLE."
> That is tidy, but I don't think it suffers much if it becomes "everything
> you can say with RETURNS TABLE is something you can equivalently say with
> OUT parameters, but there is one thing for historical reasons you can't say
> with RETURNS TABLE, and if you need to say that, with OUT params you can."
>
> > Do you have some real use cases, where proposed functionality will carry
> > some benefit?
>
> The most general might be a refactoring situation where you start with
> something producing a two-component record and one of those goes away, and
> you want to make the minimally invasive changes. Going through containing
> queries to add or remove row() or .foo would be more invasive.
>

I think so on SQL level, the difference should be minimal.

> I often am coming from the position of a PL maintainer, where my aim
> is to present an accurate picture of what is going on in PostgreSQL
> to people who are thinking in Java, and to support them with language
> constructs that will do what they expect. I happened to notice today
> that I am generating SQL that won't succeed if a Java function declares
> a one-component record result. Ok, so that's a bug I have to fix, and
> document what the real behavior is. Beyond that, if I could also say
> "if a one component record is really what you want, then write *this*",
> I think that would be good.
>

I understand. PLpgSQL does this magic implicitly, so users don't need to
solve it, but the complexity of PLpgSQL (some routines) are significantly
higher and some features cannot be implemented, because some semantics are
ambiguous. Unfortunately, there can be performance differences although the
behaviour on SQL level will be the same. And still it is an open question
how much slower it is to work with one column composite than with scalar.

>
> It seems like something that would entail a very easy change in the docs.
> The paragraph that now says
>
> 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.
>
> it could simply say
>
> 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: always RECORD if there are multiple output
> parameters. For exactly one output parameter, there is a choice:
> the same type as the single output parameter (which is the default
> if the clause is omitted), or RECORD if the function should really
> return a composite type with one component.
>

+1

Regards

Pavel

>
> Regards,
> -Chap
>