Re: [HACKERS] issue: record or row variable cannot be part ofmultiple-item INTO list

2017-05-13 22:20 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

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

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