Thread: Equivalent syntax of PL/SQL using array in PL/pgSQL
Hi all, I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause. Is there any equivalent syntax in PL/pgSQL to solve it. ------ rec typ[]; (typ[1]).t1 := 1; ------ typ is type which was created by command below. Create type typ as( t1 interger, t2 text); I am migrating data from Oracle to PostgreSQL and encounter this issue. Thanks, Huong,
On 2013-11-14 10:13, Dang Minh Huong wrote: > Hi all, > > I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause. > Is there any equivalent syntax in PL/pgSQL to solve it. > > ------ > rec typ[]; > (typ[1]).t1 := 1; > ------ > > typ is type which was created by command below. > > Create type typ as( > t1 interger, > t2 text); > > I am migrating data from Oracle to PostgreSQL and encounter this issue. > > Thanks, > Huong, > If typ is the type and rec is the variable, do you mean to access rec[1]?
Hi, 2013/11/15 0:20、Elliot <yields.falsehood@gmail.com> のメッセージ: >> On 2013-11-14 10:13, Dang Minh Huong wrote: >> Hi all, >> >> I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause. >> Is there any equivalent syntax in PL/pgSQL to solve it. >> >> ------ >> rec typ[]; >> (typ[1]).t1 := 1; >> ------ >> >> typ is type which was created by command below. >> >> Create type typ as( >> t1 interger, >> t2 text); >> >> I am migrating data from Oracle to PostgreSQL and encounter this issue. >> >> Thanks, >> Huong, > If typ is the type and rec is the variable, do you mean to access rec[1]? Sorry for this miss. Yes, typ is the type and rec is the variable. I only want to assign a value to rec[1].t1. Thanks, Huong,
On 2013-11-14 10:32, Dang Minh Huong wrote: > Hi, > > 2013/11/15 0:20、Elliot <yields.falsehood@gmail.com> のメッセージ: > >>> On 2013-11-14 10:13, Dang Minh Huong wrote: >>> Hi all, >>> >>> I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause. >>> Is there any equivalent syntax in PL/pgSQL to solve it. >>> >>> ------ >>> rec typ[]; >>> (typ[1]).t1 := 1; >>> ------ >>> >>> typ is type which was created by command below. >>> >>> Create type typ as( >>> t1 interger, >>> t2 text); >>> >>> I am migrating data from Oracle to PostgreSQL and encounter this issue. >>> >>> Thanks, >>> Huong, >> If typ is the type and rec is the variable, do you mean to access rec[1]? > Sorry for this miss. > Yes, typ is the type and rec is the variable. > I only want to assign a value to rec[1].t1. > > Thanks, > Huong, I can't recall a source on this but I'm not sure you can assign to composite types' members in plpgsql (unlike in straight sql where update set rec.t1 := 1 is valid). You can build the entire record at once like "rec[1] := (1, null)::typ;".
bocap wrote >>> (rec[1]).t1 := 1; You need to stay one-level higher and re-build the entire typ entry then assign it back to the array at the same position. rec[1] = (1, rec[1].t2)::typ; David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Equivalent-syntax-of-PL-SQL-using-array-in-PL-pgSQL-tp5778355p5778364.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi, 2013/11/15 0:38、Elliot <yields.falsehood@gmail.com> のメッセージ: >> On 2013-11-14 10:32, Dang Minh Huong wrote: >> Hi, >> >> 2013/11/15 0:20、Elliot <yields.falsehood@gmail.com> のメッセージ: >> >>>> On 2013-11-14 10:13, Dang Minh Huong wrote: >>>> Hi all, >>>> >>>> I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause. >>>> Is there any equivalent syntax in PL/pgSQL to solve it. >>>> >>>> ------ >>>> rec typ[]; >>>> (typ[1]).t1 := 1; >>>> ------ >>>> >>>> typ is type which was created by command below. >>>> >>>> Create type typ as( >>>> t1 interger, >>>> t2 text); >>>> >>>> I am migrating data from Oracle to PostgreSQL and encounter this issue. >>>> >>>> Thanks, >>>> Huong, >>> If typ is the type and rec is the variable, do you mean to access rec[1]? >> Sorry for this miss. >> Yes, typ is the type and rec is the variable. >> I only want to assign a value to rec[1].t1. >> >> Thanks, >> Huong, > > I can't recall a source on this but I'm not sure you can assign to > composite types' members in plpgsql I think so too. > (unlike in straight sql where update > set rec.t1 := 1 is valid). You can build the entire record at once like > "rec[1] := (1, null)::typ;". > But if do like that, the rec[1].t2 will replaced by null. Is there another way? Thanks, Huong,
Hi, 2013/11/15 0:43、David Johnston <polobo@yahoo.com> のメッセージ: > bocap wrote >>>> (rec[1]).t1 := 1; > > You need to stay one-level higher and re-build the entire typ entry then > assign it back to the array at the same position. > > rec[1] = (1, rec[1].t2)::typ; > Thanks. I think, i can solve it with this way. > David J. > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Equivalent-syntax-of-PL-SQL-using-array-in-PL-pgSQL-tp5778355p5778364.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Regards, Huong,
On 2013-11-14 10:56, Dang Minh Huong wrote: > Hi, > > 2013/11/15 0:38、Elliot <yields.falsehood@gmail.com> のメッセージ: > >>> On 2013-11-14 10:32, Dang Minh Huong wrote: >>> Hi, >>> >>> 2013/11/15 0:20、Elliot <yields.falsehood@gmail.com> のメッセージ: >>> >>>>> On 2013-11-14 10:13, Dang Minh Huong wrote: >>>>> Hi all, >>>>> >>>>> I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause. >>>>> Is there any equivalent syntax in PL/pgSQL to solve it. >>>>> >>>>> ------ >>>>> rec typ[]; >>>>> (typ[1]).t1 := 1; >>>>> ------ >>>>> >>>>> typ is type which was created by command below. >>>>> >>>>> Create type typ as( >>>>> t1 interger, >>>>> t2 text); >>>>> >>>>> I am migrating data from Oracle to PostgreSQL and encounter this issue. >>>>> >>>>> Thanks, >>>>> Huong, >>>> If typ is the type and rec is the variable, do you mean to access rec[1]? >>> Sorry for this miss. >>> Yes, typ is the type and rec is the variable. >>> I only want to assign a value to rec[1].t1. >>> >>> Thanks, >>> Huong, >> I can't recall a source on this but I'm not sure you can assign to >> composite types' members in plpgsql > I think so too. > >> (unlike in straight sql where update >> set rec.t1 := 1 is valid). You can build the entire record at once like >> "rec[1] := (1, null)::typ;". >> > But if do like that, the rec[1].t2 will replaced by null. > Is there another way? > > Thanks, > Huong, > Yes - see David Johnston's response
On Thu, Nov 14, 2013 at 7:13 AM, Dang Minh Huong <kakalot49@gmail.com> wrote:
> I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause.
> Is there any equivalent syntax in PL/pgSQL to solve it.
>
> ------
> rec typ[];
> (typ[1]).t1 := 1;
> ------
You can easily make different tricks with records using the hstore
module [1]. Just like this:
[local]:5432 grayhemp@grayhemp=# \d a
Table "public.a"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
n | name |
[local]:5432 grayhemp@grayhemp=# do $$
declare rec a[];
begin
rec[1] := (1, 'a')::a;
rec[2] := (2, 'b')::a;
rec[1] := rec[1] #= (hstore('id', 3::text) || hstore('n', null));
raise info '% %', rec[1].id, rec[1].n;
end $$;
INFO: 3 <NULL>
DO
[1] http://www.postgresql.org/docs/9.3/static/hstore.html
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
Thank you all for your support.
2013/11/15 3:40、Sergey Konoplev <gray.ru@gmail.com> のメッセージ:
>> On Thu, Nov 14, 2013 at 7:13 AM, Dang Minh Huong <kakalot49@gmail.com> wrote:
>> I am using PostgreSQL 9.1 and get a syntax error with the following PL/pgSQL clause.
>> Is there any equivalent syntax in PL/pgSQL to solve it.
>>
>> ------
>> rec typ[];
>> (typ[1]).t1 := 1;
>> ------
>
> You can easily make different tricks with records using the hstore
> module [1]. Just like this:
>
> [local]:5432 grayhemp@grayhemp=# \d a
> Table "public.a"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer | not null
> n | name |
>
> [local]:5432 grayhemp@grayhemp=# do $$
> declare rec a[];
> begin
> rec[1] := (1, 'a')::a;
> rec[2] := (2, 'b')::a;
> rec[1] := rec[1] #= (hstore('id', 3::text) || hstore('n', null));
> raise info '% %', rec[1].id, rec[1].n;
> end $$;
> INFO: 3 <NULL>
> DO
>
> [1] http://www.postgresql.org/docs/9.3/static/hstore.html
>
Thanks, i will try it.
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray.ru@gmail.com
Thanks,
Huong,