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

2017-05-14 5:04 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2017-05-13 22:20 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> > I am working on migration large Oracle application to Postgres. When I
>> > started migration procedures with OUT parameters I found following limit
>>
>> > "record or row variable cannot be part of multiple-item INTO list"
>>
>> 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.
>
>
> I don't think so. The semantics should be same like now.
>
> now, the output (s1,s2,s3) can be assigned to
>
> 1. scalar variables - implemented with aux row variable (s1,s2,s3) ->
> r(ts1,ts2,ts3)
> 2. record - (s1, s2, s3) -> rec(s1,s2,s3)
> 3. row - (s1,s2,s3) -> r(s1,s2,s3)
>
> If we allow composite values there, then situation is same
>
> 1. (s1, c2, s3, c4) -> r(ts1, tc2, ts3, tc4)
> 2. (s1, c2, s3, c4) -> rec(s1, c2, s3, c4)
> 3. (s1, c2, s3, c4) -> row(s1, c2, s3, c4)
>
> So there are not any inconsistency if we use rule
>
> 1. if there is one target, use it
> 2. if there are more target, create aux row variable
>
> Same technique is used for function output - build_row_from_vars - and
> there are not any problem.
>
> If you try assign composite to scalar or scalar to composite, then the
> assignment should to fail. But when statement is correct, then this invalid
> assignments should not be there.
>
>
>>
>> Yeah, we could invent some semantics or other, but I think it would
>> mostly be a foot-gun for unwary programmers.
>>
>> We do allow you to write out the columns individually for such cases:
>>
>> SELECT ... INTO v1, rowvar.c1, rowvar.c2, rowvar.c3, v2 ...
>>
>
> It doesn't help to performance and readability (and maintainability) for
> following cases
>
> There are often pattern
>
> PROCEDURE p(..., OUT r widetab%ROWTYPE, OUT errordesc COMPOSITE)
>
> Now there is a workaround
>
> SELECT * FROM p() INTO auxrec;
> r := auxrec.widetab;
> errordesc := auxrec.errordesc;
>
> But it creates N (number of OUT variables) of assignments commands over
> records.
>
> If this workaround is safe, then implementation based on aux row variable
> should be safe too, because it is manual implementation.
>
>
>
>>
>> and I think it's better to encourage people to stick to that.
>
>
> I don't think so using tens OUT variables is some nice, but current behave
> is too restrictive. More, I didn't find a case, where current
> implementation should not work (allow records needs some work).
>

here is patch

all regress tests passed

Regards

Pavel

>
>
>>
>> regards, tom lane
>>
>
>