Обсуждение: BUG #14330: can not select into `composite data types` in plpgsql
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMzMApMb2dnZWQgYnk6ICAg ICAgICAgIGx4IHpvdQpFbWFpbCBhZGRyZXNzOiAgICAgIHpvdWx4MTk4MkAx NjMuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjEKT3BlcmF0aW5nIHN5 c3RlbTogICB3aW43ICA2LjEuNzYwMQpEZXNjcmlwdGlvbjogICAgICAgIAoK SGksDQogICAgd2hlbiBpIHVzZSBjb21wb3NpdGUgdHlwZSBpbiBwbHBnc3Fs LCBpIGZvdW5kIGl0IGRvZXMndCB3b3JrIHdoZW4gaQpzZWxlY3QgYSBjb21w b3NpdGUgdHlwZSBjb2x1bW4gaW50byBhIGNvbXBvc2l0ZSB0eXBlIHZhcmlh YmxlLg0KICAgIEJ1dCBpdCBjYW4gd29yayB3ZWxsIHdoZW4gaSB1c2UgYW5v dGhlciBzdHlsZSBsaWtlIHRoaXM6DQogICAgc2VsZWN0ICh5KS5hLCh5KS5i IGludG8gdG1wIGZyb20gdC4uLi4NCmZvbGxvd2luZyBpcyBhIGZ1bGwgZXhh bXBsZS4gVGhhbmtzLiANCg0KY3JlYXRlIHR5cGUgbXl0eXBlKGEgaW50LCBi IHRleHQpOw0KY3JlYXRlIHRhYmxlIG15dGFiKHggaW50LCB5IG15dHlwZSk7 DQppbnNlcnQgaW50byBteXRhYiB2YWx1ZXMoMSwgKDEsICdhYmNkJykpOw0K ZG8gJCQNCmRlY2xhcmUNCgl0bXAgbXl0eXBlOw0KYmVnaW4NCgktLUVSUk9S OiAgaW52YWxpZCBpbnB1dCBzeW50YXggZm9yIGludGVnZXI6ICIoMSxhc2Rk ZCkiDQoJLS1DT05URVhUOiAgUEwvcGdTUUwgZnVuY3Rpb24gaW5saW5lX2Nv ZGVfYmxvY2sgbGluZSA2IGF0IFNRTCBzdGF0ZW1lbnQNCglzZWxlY3QgeSBp bnRvIHN0cmljdCB0bXAgZnJvbSBteXRhYjsNCglyYWlzZSBub3RpY2UgJyUs JScsICh0bXApLmEsICh0bXApLmI7DQplbmQ7DQokJDsNCgoK
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 >
> 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>
Hi2016-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.RegardsPavel 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
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 >> > > > > >