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

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).

>
> regards, tom lane
>