From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: issue: record or row variable cannot be part of multiple-item INTO list |
Date: | 2017-05-14 03:04:45 |
Message-ID: | CAFj8pRDe9b8HDE+C1xR3a_oS0=SNB-WGVxiX_9q09bo81bS4uA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-05-14 03:32:29 | Re: Latest Data::Dumper breaks hstore_plperl regression test |
Previous Message | Mark Dilger | 2017-05-14 03:03:39 | Event triggers + table partitioning cause server crash in current master |