Equivalent syntax of PL/SQL using array in PL/pgSQL

Started by Dang Minh Huongover 12 years ago10 messagesgeneral

kakalot49@gmail.com

over 12 years ago

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,

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

Elliot

yields.falsehood@gmail.com

over 12 years ago

In reply to: Dang Minh Huong (#1)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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]?

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

Dang Minh Huong

kakalot49@gmail.com

over 12 years ago

In reply to: Elliot (#2)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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,

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

Elliot

yields.falsehood@gmail.com

over 12 years ago

In reply to: Dang Minh Huong (#3)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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

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

David G. Johnston

david.g.johnston@gmail.com

over 12 years ago

In reply to: Dang Minh Huong (#3)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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.

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

Dang Minh Huong

kakalot49@gmail.com

over 12 years ago

In reply to: Elliot (#4)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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,

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

Dang Minh Huong

kakalot49@gmail.com

over 12 years ago

In reply to: David G. Johnston (#5)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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,

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

Elliot

yields.falsehood@gmail.com

over 12 years ago

In reply to: Dang Minh Huong (#6)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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

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

Sergey Konoplev

gray.ru@gmail.com

over 12 years ago

In reply to: Dang Minh Huong (#1)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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]http://www.postgresql.org/docs/9.3/static/hstore.html. Just like this:

[local]:5432 grayhemp@grayhemp=# do $$
declare rec a[];
begin
rec[1]http://www.postgresql.org/docs/9.3/static/hstore.html := (1, 'a')::a;
rec[2] := (2, 'b')::a;
rec[1]http://www.postgresql.org/docs/9.3/static/hstore.html := rec[1]http://www.postgresql.org/docs/9.3/static/hstore.html #= (hstore('id', 3::text) || hstore('n', null));
raise info '% %', rec[1]http://www.postgresql.org/docs/9.3/static/hstore.html.id, rec[1]http://www.postgresql.org/docs/9.3/static/hstore.html.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

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

#10

Dang Minh Huong

kakalot49@gmail.com

over 12 years ago

In reply to: Sergey Konoplev (#9)

Re: Equivalent syntax of PL/SQL using array in PL/pgSQL

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,

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