2017-05-14 5:04 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2017-05-13 22:20 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>: > >> Pavel Stehule <pavel.steh...@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). >
here is patch all regress tests passed Regards Pavel > > >> >> regards, tom lane >> > >
plpgsql-into-multitarget.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
