Pavel Stehule <pavel.stehule@gmail.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
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).
