How to store multiple rows in array .

Started by Brahmam Eswarover 8 years ago5 messagesgeneral
Jump to latest
#1Brahmam Eswar
brahmam1234@gmail.com

Hi ,

System is migrating from Oracle to Postgre SQL.
Oracle is providing BULK COLLECT INTO function to collect the multiple
records from table .

Select COL1,COL2 ,COL3 BULK COLLECT INTO LINES from Distinct_Records.

LINES IS TABLE OF TABLE1 (Defined lines as IS TABLE OF type).

In PotGres:

INV_LINES_T validate_crtr_line_items$inv_lines_rt ARRAY;
L_INV_LINES INV_LINES_T%TYPE;
L_INV_LINES$temporary_record ap.validate_crtr_line_items$inv_lines_rt;

I'm trying to collect the records in L_INV_LINES

SELECT ARRAY (SELECT COL1,COL2,COL3 FROM Distinct_Records) INTO
L_INV_LINES;

Seems, Selecting multiple columns into an array doesn't work in PL/pgSQL .

How to collect multiple columns into array which is composite data type of
all select colums

--
Thanks & Regards,
Brahmeswara Rao J.

#2Pavel Stehule
pavel.stehule@gmail.com
In reply to: Brahmam Eswar (#1)
Re: How to store multiple rows in array .

2017-11-19 18:57 GMT+01:00 Brahmam Eswar <brahmam1234@gmail.com>:

Hi ,

System is migrating from Oracle to Postgre SQL.
Oracle is providing BULK COLLECT INTO function to collect the multiple
records from table .

Select COL1,COL2 ,COL3 BULK COLLECT INTO LINES from Distinct_Records.

LINES IS TABLE OF TABLE1 (Defined lines as IS TABLE OF type).

In PotGres:

INV_LINES_T validate_crtr_line_items$inv_lines_rt ARRAY;
L_INV_LINES INV_LINES_T%TYPE;
L_INV_LINES$temporary_record ap.validate_crtr_line_items$inv_lines_rt;

I'm trying to collect the records in L_INV_LINES

SELECT ARRAY (SELECT COL1,COL2,COL3 FROM Distinct_Records) INTO
L_INV_LINES;

Seems, Selecting multiple columns into an array doesn't work in PL/pgSQL
.

How to collect multiple columns into array which is composite data type of
all select colums

SELECT ARRAY(SELECT ROW(col1, col2, ...) INTO

Show quoted text

--
Thanks & Regards,
Brahmeswara Rao J.

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Pavel Stehule (#2)
Re: How to store multiple rows in array .

Pavel Stehule <pavel.stehule@gmail.com> writes:

2017-11-19 18:57 GMT+01:00 Brahmam Eswar <brahmam1234@gmail.com>:

How to collect multiple columns into array which is composite data type of
all select colums

SELECT ARRAY(SELECT ROW(col1, col2, ...) INTO

You probably need an explicit cast to the rowtype. That is,

declare myarray rowtypename[];
...
select array(select row(col1, ...)::rowtypename from ...) into myarray;

regards, tom lane

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

#4Brahmam Eswar
brahmam1234@gmail.com
In reply to: Tom Lane (#3)
Re: How to store multiple rows in array .

I already defined the composite type as
"validate_crtr_line_items$inv_lines_rt" with the selected
columns(COL1,COL2,COl3)

DeCLARE Block :

INV_LINES_T validate_crtr_line_items$inv_lines_rt ARRAY;
L_INV_LINES INV_LINES_T%TYPE;
L_INV_LINES$temporary_record ap.validate_crtr_line_items$inv_lines_rt;

Collecting the records into L_INV_LINES

SELECT ARRAY (SELECT ROW (COL1,COL2, COUNT(*) *AS txn_cnt* )::
ap.validate_crtr_line_items$inv_lines_rt FROM Distinct_Records GROUP BY
COL1, COL2 HAVING COUNT(*) > 1) INTO L_INV_LINES;

ERROR: syntax error at or near "AS"
LINE 73: COL1,COL2, COUNT(*) AS txn_cnt...

Why "AS" is throwing an error ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Brahmam Eswar (#4)
Re: How to store multiple rows in array .

brahmesr <brahmam1234@gmail.com> writes:

SELECT ARRAY (SELECT ROW (COL1,COL2, COUNT(*) *AS txn_cnt* )::
ap.validate_crtr_line_items$inv_lines_rt FROM Distinct_Records GROUP BY
COL1, COL2 HAVING COUNT(*) > 1) INTO L_INV_LINES;

ERROR: syntax error at or near "AS"
LINE 73: COL1,COL2, COUNT(*) AS txn_cnt...

Why "AS" is throwing an error ?

"AS" is part of SELECT-list syntax, not ROW(...) syntax.

Even if it were allowed in ROW(), it would be totally pointless in
this context, because when you cast the ROW() result to the
ap.validate_crtr_line_items$inv_lines_rt composite type, that type
is what determines the column names.

regards, tom lane

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