Thread: [GENERAL] How to store multiple rows in array .
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.
Brahmeswara Rao J.
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_LINESSELECT 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.
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
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
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