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

2017-10-02 Thread Robert Haas 
On Mon, Oct 2, 2017 at 12:44 PM, Tom Lane  wrote:
>> And I'm saying - that argument is bogus.  Regardless of what people
>> want or what we have historically done in the case where the
>> record/row is the only INTO target, when there are multiple targets it
>> seems clear that they want to match up the query's output columns with
>> the INTO targets 1:1.  So let's just do that.
>
> Arguing that that's what people want (even if I granted your argument,
> which I do not) does not make the inconsistency magically disappear,
> nor will it stop people from being confused by that inconsistency.
> Furthermore, if we do it like this, we will be completely backed into
> a backwards-compatibility corner if someone does come along and say
> "hey, I wish I could do the other thing".

That is not really true.  Even if we define INTO a, b, c as I am
proposing (and Pavel, too, I think), I think we can later define INTO
*a, INTO (a), INTO a ..., INTO a MULTIPLE, INTO a STRANGELY, and INTO
%@!a??ppp#zorp to mean anything we like (unless one or more of those
already have some semantics already, in which case pick something that
doesn't).

If we're really on the fence about which behavior people will want, we
could implement both with a syntax marker for each, say SELECT ...
INTO a #rhaas for the behavior I like and SELECT ... INTO a #tgl_ftw
for the other behavior, and require specifying one of those
decorations.  Maybe that's more or less what you were proposing.  If
we're going to have a default, though, I think it should be the one
you described as "inconsistent with the single row case" rather than
the one you described as "very bug-prone", because I agree with those
characterizations but feel that the latter is a much bigger problem
than the former and, again, not what anybody actually wants.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-10-02 Thread Pavel Stehule 
2017-10-02 18:44 GMT+02:00 Tom Lane :

> Robert Haas  writes:
> > On Mon, Oct 2, 2017 at 12:28 PM, Tom Lane  wrote:
> >> I'm not sure if that's true or not.  I am sure, though, that since
> >> we've done B for twenty years we can't just summarily change to A.
>
> > I agree, but so what?  You said that we couldn't adopt Pavel's
> > proposal for this reason:
>
> > ===
> > 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.
> > ===
>
> > And I'm saying - that argument is bogus.  Regardless of what people
> > want or what we have historically done in the case where the
> > record/row is the only INTO target, when there are multiple targets it
> > seems clear that they want to match up the query's output columns with
> > the INTO targets 1:1.  So let's just do that.
>
> Arguing that that's what people want (even if I granted your argument,
> which I do not) does not make the inconsistency magically disappear,
> nor will it stop people from being confused by that inconsistency.
> Furthermore, if we do it like this, we will be completely backed into
> a backwards-compatibility corner if someone does come along and say
> "hey, I wish I could do the other thing".
>
> I'm fine with doing something where we add new notation to dispel
> the ambiguity.  I don't want to put in another layer of inconsistency
> and then have even more backwards-compatibility problems constraining
> our response to the inevitable complaints.
>

I didn't talk about record type. I talked just only about composite
variables (ROW in our terminology).

I don't think so for this case the special syntax is necessary, although we
can use a parallel assignment with different semantics for this case.

What is a motivation for this thread?

I had to migrate lot of Oracle procedures where was usually two OUT
variables - first - known composite type (some state variable), and second
- result (text or int variable). Now, the CALL of this function in Postgres
is:

SELECT fx() INTO rec;
var_state := rec.state;
var_result := rec.result;

It works, Ora2pg supports it, plpgsql_check is able to check it, but it is
not elegant and less readable.

So, when target is not clean REC or ROW, I am think so we can allow
assignment with few limits

1. The REC type should not be used
2. The target and source fields should be same - this assignment should not
be tolerant like now. Because, this situation is not supported now, there
is not a compatibility risk

Some modern and now well known languages like GO supports parallel
assignment. Can be it the special syntax requested by Tom?

So there are two proposals:

1. Implement safe restrictive SELECT INTO where target can be combination
of REC or scalars
2. Parallel assignment with new behave, that allows any list of REC, ROW or
scalar as target - but composite should be attached to composite var, and
scalar to scalar. List of scalars should be disallowed as target for
composite value should be a) disallowed every time, b) disallowed when some
target var is a composite.

The differences between assign command and INTO command can be messy too.
So the best solution should be one rules for := and INTO - but I am not
sure if it is possible

Comments?

Regards

Pavel



> regards, tom lane
>

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

2017-10-02 Thread Tom Lane 
Robert Haas  writes:
> On Mon, Oct 2, 2017 at 12:28 PM, Tom Lane  wrote:
>> I'm not sure if that's true or not.  I am sure, though, that since
>> we've done B for twenty years we can't just summarily change to A.

> I agree, but so what?  You said that we couldn't adopt Pavel's
> proposal for this reason:

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

> And I'm saying - that argument is bogus.  Regardless of what people
> want or what we have historically done in the case where the
> record/row is the only INTO target, when there are multiple targets it
> seems clear that they want to match up the query's output columns with
> the INTO targets 1:1.  So let's just do that.

Arguing that that's what people want (even if I granted your argument,
which I do not) does not make the inconsistency magically disappear,
nor will it stop people from being confused by that inconsistency.
Furthermore, if we do it like this, we will be completely backed into
a backwards-compatibility corner if someone does come along and say
"hey, I wish I could do the other thing".

I'm fine with doing something where we add new notation to dispel
the ambiguity.  I don't want to put in another layer of inconsistency
and then have even more backwards-compatibility problems constraining
our response to the inevitable complaints.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-10-02 Thread Robert Haas 
On Mon, Oct 2, 2017 at 12:28 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Sep 19, 2017 at 3:18 PM, Tom Lane  wrote:
>>> That's certainly a case that we ought to support somehow.  The problem is
>>> staying reasonably consistent with the two-decades-old precedent of the
>>> existing behavior for one target variable.
>
>> My point is that you objected to Pavel's proposal saying "it's not
>> clear whether users want A or B".  But I think they always want A.
>
> I'm not sure if that's true or not.  I am sure, though, that since
> we've done B for twenty years we can't just summarily change to A.

I agree, but so what?  You said that we couldn't adopt Pavel's
proposal for this reason:

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

And I'm saying - that argument is bogus.  Regardless of what people
want or what we have historically done in the case where the
record/row is the only INTO target, when there are multiple targets it
seems clear that they want to match up the query's output columns with
the INTO targets 1:1.  So let's just do that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-10-02 Thread Tom Lane 
Robert Haas  writes:
> On Tue, Sep 19, 2017 at 3:18 PM, Tom Lane  wrote:
>> That's certainly a case that we ought to support somehow.  The problem is
>> staying reasonably consistent with the two-decades-old precedent of the
>> existing behavior for one target variable.

> My point is that you objected to Pavel's proposal saying "it's not
> clear whether users want A or B".  But I think they always want A.

I'm not sure if that's true or not.  I am sure, though, that since
we've done B for twenty years we can't just summarily change to A.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-10-02 Thread Robert Haas 
On Tue, Sep 19, 2017 at 3:18 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> I think the fact that single-target INTO lists and multiple-target
>> INTO lists are handled completely differently is extremely poor
>> language design.  It would have been far better, as you suggested
>> downthread, to have added some syntax up front to let people select
>> the behavior that they want, but I think there's little hope of
>> changing this now without creating even more pain.
>
> How so?  The proposal I gave is fully backwards-compatible.  It's
> likely not the way we'd do it in a green field, but we don't have
> a green field.
>
>> I have a really hard time, however, imagining that anyone writes
>> SELECT a, b, c, d, e, f, g, h, i, j, k INTO x, y, z and wants some of
>> a-k to go into x, some more to go into y, and some more to go into z
>> (and heaven help you if you drop a column from x or y -- now the whole
>> semantics of the query change, yikes).  What's reasonable is to write
>> SELECT a, b, c INTO x, y, z and have those correspond 1:1.
>
> That's certainly a case that we ought to support somehow.  The problem is
> staying reasonably consistent with the two-decades-old precedent of the
> existing behavior for one target variable.

My point is that you objected to Pavel's proposal saying "it's not
clear whether users want A or B".  But I think they always want A.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-10-01 Thread Daniel Gustafsson 
> On 20 Sep 2017, at 01:05, David G. Johnston  
> wrote:
> 
> On Tue, Sep 19, 2017 at 11:29 AM, Tom Lane  > wrote:
> ​T​hat​ ​doesn't work today, and this patch doesn't fix it, but it does create
> enough confusion that we never would be able to fix it.
> 
> I'd be much happier if there were some notational difference
> between I-want-the-composite-variable-to-absorb-a-composite-column
> and I-want-the-composite-variable-to-absorb-N-scalar-columns.
> For backwards compatibility with what happens now, the latter would
> have to be the default.
> 
> ​So, using "()" syntax​
> 
> s,t: scalar text
> c,d: (text, text)
> 
> treat all numbers below as text; and the ((1,2),) as ("(1,2)",)
> 
> A. SELECT 1 INTO s; -- today 1, this patch is the same
> 
> B. SELECT 1, 2 INTO s; -- today 1, this patch is the same
> 
> C. SELECT 1, 2 INTO (s); -- ERROR syntax - scalars cannot be tagged with (), 
> this patch N/A
>  
> D. SELECT 1, 2 INTO s, t; -- today 1, 2, this patch is the same
> 
> E. SELECT 1, 2 INTO c; -- today (1,2), this patch is the same
> 
> F. SELECT 1, 2 INTO (c); --ERROR "1" cannot be converted to (text, text), 
> this patch N/A
> 
> 1. SELECT (1,2) INTO c; -- today ((1,2),); this patch is the same
> 
> 2. SELECT (1,2) INTO (c); -- (1,2) -- this patch N/A
> 
> 3. SELECT (1,2),(3,4) INTO c,d; -- ERROR syntax -- this patch gives [I 
> think...it can be made to give] (1,2),(3,4)
> 
> 4. SELECT (1,2),(3,4) INTO c,(d); -- ERROR syntax -- this patch N/A
> 
> 5. SELECT (1,2),(3,4) INTO (c),d; -- ERROR syntax -- this patch N/A
> 
> 6. SELECT (1,2),(3,4) INTO (c),(d); -- (1,2),(3,4) -- this patch N/A
> 
> !. SELECT 1, (2,3), 4 INTO s, (c), t -- 1, (2,3), 4 -- this patch N/A
> @. SELECT 1, 2, 3, 4 INTO s, (c), t -- ERROR "2" cannot be made into (text, 
> text) -- this patch N/A
> 
> IOW, this patch, if "c" is the only target (#1) and is composite pretend the 
> user wrote "INTO c.1, c.2" and assign each output column as a scalar in 
> one-by-one fashion.  If "c" is not the only target column (#3) assign a 
> single output column to it.  This maintains compatibility and clean syntax at 
> the cost of inconsistency.
> 
> The alternate, backward compatible, option introduces mandatory () in the 
> syntax for all composite columns in a multi-variable target (# 3-5 errors, #6 
> valid) while it changes the behavior if present on a single variable target 
> (#1 vs #2).
> 
> So, allowing #3 to work makes implementing #2 even more unappealing.  Making 
> only #2 and #6 work seems like a reasonable alternative position.
> 
> The last option is to fix #1 to return (1,2) - cleanly reporting an error if 
> possible, must like we just did with SRFs, and apply the patch thus gaining 
> both consistency and a clean syntax at the expense of limited backward 
> incompatibility.
> 
> Arrays not considered; single-column composites might end up looking like 
> scalars when presented to a (c) target.
> 
> Hope this helps someone besides me understand the problem space.

Based on the discussion in this thread I’ve moved this patch to the next CF
with the new status Waiting for author, as it seems to need a revised version.

cheers ./daniel



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-09-19 Thread Tom Lane 
Robert Haas  writes:
> I think the fact that single-target INTO lists and multiple-target
> INTO lists are handled completely differently is extremely poor
> language design.  It would have been far better, as you suggested
> downthread, to have added some syntax up front to let people select
> the behavior that they want, but I think there's little hope of
> changing this now without creating even more pain.

How so?  The proposal I gave is fully backwards-compatible.  It's
likely not the way we'd do it in a green field, but we don't have
a green field.

> I have a really hard time, however, imagining that anyone writes
> SELECT a, b, c, d, e, f, g, h, i, j, k INTO x, y, z and wants some of
> a-k to go into x, some more to go into y, and some more to go into z
> (and heaven help you if you drop a column from x or y -- now the whole
> semantics of the query change, yikes).  What's reasonable is to write
> SELECT a, b, c INTO x, y, z and have those correspond 1:1.

That's certainly a case that we ought to support somehow.  The problem is
staying reasonably consistent with the two-decades-old precedent of the
existing behavior for one target variable.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-09-19 Thread Tom Lane 
"David G. Johnston"  writes:
> On Tuesday, September 19, 2017, Tom Lane  wrote:
>> I'd be much happier if there were some notational difference
>> between I-want-the-composite-variable-to-absorb-a-composite-column
>> and I-want-the-composite-variable-to-absorb-N-scalar-columns.

> If we change to considering exactly one output column for each target var
> this seems unnecessary.

Breaking backwards compatibility to that extent seems like a nonstarter.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-09-19 Thread David G. Johnston 
On Tuesday, September 19, 2017, Tom Lane  wrote:

> Pavel Stehule > writes:
> > 2017-09-14 12:33 GMT+02:00 Anthony Bykov  >:
> >> As far as I understand, this patch adds functionality (correct me if I'm
> >> wrong) for users. Shouldn't there be any changes in doc/src/sgml/ with
> the
> >> description of new functionality?
>
> > It removes undocumented limit. I recheck plpgsql documentation and there
> > are not any rows about prohibited combinations of data types.
>
> I remain of the opinion that this patch is a fundamentally bad idea.
> It creates an inconsistency between what happens if the INTO target list
> is a single record/row variable (it absorbs all the columns of the query
> output) and what happens if a record/row variable is part of a
> multi-variable target list (now it just absorbs one column, which had
> better be composite).  That's going to confuse people, especially since
> you haven't documented it.  But even with documentation, it doesn't seem
> like good design.  Aside from being inconsistent, it doesn't cover all
> the cases --- what if you have just one query output column, that is
> composite, and you'd like it to go into a composite variable?  That
> doesn't work today, and this patch doesn't fix it, but it does create
> enough confusion that we never would be able to fix it.
>
>
I think it's worth definitively addressing the limitations
noted, documenting how they are resolved/handled, and then give the
programmer more flexibility while, IMO, marginally increasing complexity.
For me we've programmed the "convenience case" and punted on the
technically correct solution.  i.e., we could have chosen to force the user
to write select (c1, c2)::ct into vct.

I'd be much happier if there were some notational difference
> between I-want-the-composite-variable-to-absorb-a-composite-column
> and I-want-the-composite-variable-to-absorb-N-scalar-columns.


If we change to considering exactly one output column for each target var
this seems unnecessary.  Then the one special case is today's single
composite column target and multiple output columns.  If there is only one
select column it has to be composite.

David J.

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

2017-09-19 Thread Robert Haas 
On Sat, May 13, 2017 at 4:20 PM, Tom Lane  wrote:
> 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.

Maybe I'm just confused here, but do you think that anyone at all
wants the second behavior?

I think the fact that single-target INTO lists and multiple-target
INTO lists are handled completely differently is extremely poor
language design.  It would have been far better, as you suggested
downthread, to have added some syntax up front to let people select
the behavior that they want, but I think there's little hope of
changing this now without creating even more pain.

I have a really hard time, however, imagining that anyone writes
SELECT a, b, c, d, e, f, g, h, i, j, k INTO x, y, z and wants some of
a-k to go into x, some more to go into y, and some more to go into z
(and heaven help you if you drop a column from x or y -- now the whole
semantics of the query change, yikes).  What's reasonable is to write
SELECT a, b, c INTO x, y, z and have those correspond 1:1.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-05-14 Thread Pavel Stehule 
2017-05-14 5:04 GMT+02:00 Pavel Stehule :

>
>
> 2017-05-13 22:20 GMT+02:00 Tom Lane :
>
>> Pavel Stehule  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

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

2017-05-13 Thread Pavel Stehule 
2017-05-13 22:20 GMT+02:00 Tom Lane :

> Pavel Stehule  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
>

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

2017-05-13 Thread Tom Lane 
Pavel Stehule  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.

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

and I think it's better to encourage people to stick to that.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

2017-05-13 Thread Pavel Stehule 
Hi

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"

I checked code and it looks so this limit is not necessary for ROW types
(what is enough for migration from Oracle, where REC is not available).

Do you think so this limit is necessary for ROW types?

@@ -3368,19 +3368,7 @@ read_into_target(PLpgSQL_rec **rec, PLpgSQL_row
**row, bool *strict)
switch (tok)
{
case T_DATUM:
-   if (yylval.wdatum.datum->dtype == PLPGSQL_DTYPE_ROW)
-   {
-   check_assignable(yylval.wdatum.datum, yylloc);
-   *row = (PLpgSQL_row *) yylval.wdatum.datum;
-
-   if ((tok = yylex()) == ',')
-   ereport(ERROR,
-   (errcode(ERRCODE_SYNTAX_ERROR),
-errmsg("record or row variable cannot be part
of multiple-item INTO list"),
-parser_errposition(yylloc)));
-   plpgsql_push_back_token(tok);
-   }
-   else if (yylval.wdatum.datum->dtype == PLPGSQL_DTYPE_REC)


Regards

Pavel