Re: [GENERAL] How to store multiple rows in array .
brahmesr 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
Re: [GENERAL] 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
Re: [GENERAL] How to store multiple rows in array .
Pavel Stehule writes: > 2017-11-19 18:57 GMT+01:00 Brahmam Eswar : >> 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 .
2017-11-19 18:57 GMT+01:00 Brahmam Eswar : > 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. >
[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.