Re: issue: record or row variable cannot be part of multiple-item INTO list

2017-10-02 18:44 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mon, Oct 2, 2017 at 12:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> I'm not sure if that's true or not. I am sure, though, that since
> >> we've done B for twenty years we can't just summarily change to A.
>
> > I agree, but so what? You said that we couldn't adopt Pavel's
> > proposal for this reason:
>
> > ===
> > IIRC, the reason for disallowing that is that it's totally unclear what
> > the semantics ought to be. Is that variable a single target (demanding
> > a compatible composite-valued column from the source query), or does it
> > eat one source column per field within the record/row? The former is
> 100%
> > inconsistent with what happens if the record/row is the only INTO target;
> > while the latter would be very bug-prone, and it's especially unclear
> what
> > ought to happen if it's an as-yet-undefined record variable.
> > ===
>
> > And I'm saying - that argument is bogus. Regardless of what people
> > want or what we have historically done in the case where the
> > record/row is the only INTO target, when there are multiple targets it
> > seems clear that they want to match up the query's output columns with
> > the INTO targets 1:1. So let's just do that.
>
> Arguing that that's what people want (even if I granted your argument,
> which I do not) does not make the inconsistency magically disappear,
> nor will it stop people from being confused by that inconsistency.
> Furthermore, if we do it like this, we will be completely backed into
> a backwards-compatibility corner if someone does come along and say
> "hey, I wish I could do the other thing".
>
> I'm fine with doing something where we add new notation to dispel
> the ambiguity. I don't want to put in another layer of inconsistency
> and then have even more backwards-compatibility problems constraining
> our response to the inevitable complaints.
>

I didn't talk about record type. I talked just only about composite
variables (ROW in our terminology).

I don't think so for this case the special syntax is necessary, although we
can use a parallel assignment with different semantics for this case.

What is a motivation for this thread?

I had to migrate lot of Oracle procedures where was usually two OUT
variables - first - known composite type (some state variable), and second
- result (text or int variable). Now, the CALL of this function in Postgres
is:

SELECT fx() INTO rec;
var_state := rec.state;
var_result := rec.result;

It works, Ora2pg supports it, plpgsql_check is able to check it, but it is
not elegant and less readable.

So, when target is not clean REC or ROW, I am think so we can allow
assignment with few limits

1. The REC type should not be used
2. The target and source fields should be same - this assignment should not
be tolerant like now. Because, this situation is not supported now, there
is not a compatibility risk

Some modern and now well known languages like GO supports parallel
assignment. Can be it the special syntax requested by Tom?

So there are two proposals:

1. Implement safe restrictive SELECT INTO where target can be combination
of REC or scalars
2. Parallel assignment with new behave, that allows any list of REC, ROW or
scalar as target - but composite should be attached to composite var, and
scalar to scalar. List of scalars should be disallowed as target for
composite value should be a) disallowed every time, b) disallowed when some
target var is a composite.

The differences between assign command and INTO command can be messy too.
So the best solution should be one rules for := and INTO - but I am not
sure if it is possible

Comments?

Regards

Pavel

> regards, tom lane
>