Thread: [GENERAL] How to store multiple rows in array .

[GENERAL] How to store multiple rows in array .

From
Brahmam Eswar
Date:
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.

Re: [GENERAL] How to store multiple rows in array .

From
Pavel Stehule
Date:


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 

--
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] How to store multiple rows in array .

From
Tom Lane
Date:
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

Re: [GENERAL] How to store multiple rows in array .

From
brahmesr
Date:
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_recordap.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

Re: [GENERAL] How to store multiple rows in array .

From
Tom Lane
Date:
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