Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #14330: can not select into `composite data types` in plpgsql

BUG #14330: can not select into `composite data types` in plpgsql

From
zoulx1982@163.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMzMApMb2dnZWQgYnk6ICAg
ICAgICAgIGx4IHpvdQpFbWFpbCBhZGRyZXNzOiAgICAgIHpvdWx4MTk4MkAx
NjMuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjEKT3BlcmF0aW5nIHN5
c3RlbTogICB3aW43ICA2LjEuNzYwMQpEZXNjcmlwdGlvbjogICAgICAgIAoK
SGksDQogICAgd2hlbiBpIHVzZSBjb21wb3NpdGUgdHlwZSBpbiBwbHBnc3Fs
LCBpIGZvdW5kIGl0IGRvZXMndCB3b3JrIHdoZW4gaQpzZWxlY3QgYSBjb21w
b3NpdGUgdHlwZSBjb2x1bW4gaW50byBhIGNvbXBvc2l0ZSB0eXBlIHZhcmlh
YmxlLg0KICAgIEJ1dCBpdCBjYW4gd29yayB3ZWxsIHdoZW4gaSB1c2UgYW5v
dGhlciBzdHlsZSBsaWtlIHRoaXM6DQogICAgc2VsZWN0ICh5KS5hLCh5KS5i
IGludG8gdG1wIGZyb20gdC4uLi4NCmZvbGxvd2luZyBpcyBhIGZ1bGwgZXhh
bXBsZS4gVGhhbmtzLiANCg0KY3JlYXRlIHR5cGUgbXl0eXBlKGEgaW50LCBi
IHRleHQpOw0KY3JlYXRlIHRhYmxlIG15dGFiKHggaW50LCB5IG15dHlwZSk7
DQppbnNlcnQgaW50byBteXRhYiB2YWx1ZXMoMSwgKDEsICdhYmNkJykpOw0K
ZG8gJCQNCmRlY2xhcmUNCgl0bXAgbXl0eXBlOw0KYmVnaW4NCgktLUVSUk9S
OiAgaW52YWxpZCBpbnB1dCBzeW50YXggZm9yIGludGVnZXI6ICIoMSxhc2Rk
ZCkiDQoJLS1DT05URVhUOiAgUEwvcGdTUUwgZnVuY3Rpb24gaW5saW5lX2Nv
ZGVfYmxvY2sgbGluZSA2IGF0IFNRTCBzdGF0ZW1lbnQNCglzZWxlY3QgeSBp
bnRvIHN0cmljdCB0bXAgZnJvbSBteXRhYjsNCglyYWlzZSBub3RpY2UgJyUs
JScsICh0bXApLmEsICh0bXApLmI7DQplbmQ7DQokJDsNCgoK

Re: BUG #14330: can not select into `composite data types` in plpgsql

From
Pavel Stehule
Date:
Hi

2016-09-21 5:51 GMT+02:00 <zoulx1982@163.com>:

> The following bug has been logged on the website:
>
> Bug reference:      14330
> Logged by:          lx zou
> Email address:      zoulx1982@163.com
> PostgreSQL version: 9.5.1
> Operating system:   win7  6.1.7601
> Description:
>
> Hi,
>     when i use composite type in plpgsql, i found it does't work when i
> select a composite type column into a composite type variable.
>     But it can work well when i use another style like this:
>     select (y).a,(y).b into tmp from t....
> following is a full example. Thanks.
>
> create type mytype(a int, b text);
> create table mytab(x int, y mytype);
> insert into mytab values(1, (1, 'abcd'));
> do $$
> declare
>         tmp mytype;
> begin
>         --ERROR:  invalid input syntax for integer: "(1,asddd)"
>         --CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL
> statement
>         select y into strict tmp from mytab;
>         raise notice '%,%', (tmp).a, (tmp).b;
> end;
> $$;
>

It is not bug - although I understand so this behave is not unfriendly. The
pattern composite var := composite value is not supported due possible
ambiguous assignment internally in PLpgSQL implementation.

Regards

Pavel Stehule


>
> It is no
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

回复:Re: BUG #14330: can not select into `composite data types` in plpgsql

From
自己
Date:
> It is not bug - although I understand so this behave is not unfriendly. The pattern composite var := composite value is not supported due possible ambiguous
> assignment internally in PLpgSQL implementation.

Thank you for your reply.
It seems work well for the pattern "composite var := composite value" in plpgsql body, but not in declare sestion(default value).
Besides this, i want to know if i want to implement this pattern, which part is difficult?


2016-09-21 15:12:33，"Pavel Stehule" <pavel.stehule@gmail.com>
Hi

2016-09-21 5:51 GMT+02:00 <zoulx1982@163.com>:
The following bug has been logged on the website:

Bug reference:      14330
Logged by:          lx zou
Email address:      zoulx1982@163.com
PostgreSQL version: 9.5.1
Operating system:   win7  6.1.7601
Description:

Hi,
    when i use composite type in plpgsql, i found it does't work when i
select a composite type column into a composite type variable.
    But it can work well when i use another style like this:
    select (y).a,(y).b into tmp from t....
following is a full example. Thanks.

create type mytype(a int, b text);
create table mytab(x int, y mytype);
insert into mytab values(1, (1, 'abcd'));
do $$
declare
        tmp mytype;
begin
        --ERROR:  invalid input syntax for integer: "(1,asddd)"
        --CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL statement
        select y into strict tmp from mytab;
        raise notice '%,%', (tmp).a, (tmp).b;
end;
$$;

It is not bug - although I understand so this behave is not unfriendly. The pattern composite var := composite value is not supported due possible ambiguous assignment internally in PLpgSQL implementation.

Regards

Pavel Stehule
 

It is no

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



 

Re: BUG #14330: can not select into `composite data types` in plpgsql

From
Pavel Stehule
Date:
Hi

2016-09-21 10:08 GMT+02:00 =E8=87=AA=E5=B7=B1 <zoulx1982@163.com>:

> > It is not bug - although I understand so this behave is not unfriendly.
> The pattern composite var :=3D composite value is not supported due possi=
ble
> ambiguous
> > assignment internally in PLpgSQL implementation.
>
> Thank you for your reply.
> It seems work well for the pattern "composite var :=3D composite value" i=
n
> plpgsql body, but not in declare sestion(default value).
> Besides this, i want to know if i want to implement this pattern, which
> part is difficult?
>

the result of SQL query is result set. when you use composite type, then
result will be nested compose type. But you don't have simply access to
original query, and you don't know if you should or should not do unnesting=
.

Regards

Pavel Stehule


>
>
> 2016-09-21 15:12:33=EF=BC=8C"Pavel Stehule" <pavel.stehule@gmail.com>
>
> Hi
>
> 2016-09-21 5:51 GMT+02:00 <zoulx1982@163.com>:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      14330
>> Logged by:          lx zou
>> Email address:      zoulx1982@163.com
>> PostgreSQL version: 9.5.1
>> Operating system:   win7  6.1.7601
>> Description:
>>
>> Hi,
>>     when i use composite type in plpgsql, i found it does't work when i
>> select a composite type column into a composite type variable.
>>     But it can work well when i use another style like this:
>>     select (y).a,(y).b into tmp from t....
>> following is a full example. Thanks.
>>
>> create type mytype(a int, b text);
>> create table mytab(x int, y mytype);
>> insert into mytab values(1, (1, 'abcd'));
>> do $$
>> declare
>>         tmp mytype;
>> begin
>>         --ERROR:  invalid input syntax for integer: "(1,asddd)"
>>         --CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL
>> statement
>>         select y into strict tmp from mytab;
>>         raise notice '%,%', (tmp).a, (tmp).b;
>> end;
>> $$;
>>
>
> It is not bug - although I understand so this behave is not unfriendly.
> The pattern composite var :=3D composite value is not supported due possi=
ble
> ambiguous assignment internally in PLpgSQL implementation.
>
> Regards
>
> Pavel Stehule
>
>
>>
>> It is no
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>
>
>
>