[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:

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

Re: [GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

Hi, So I'd call this an oracle_fdw bug. It needs to postpone what it's doing here to the first normal FDW function call in a session. Thanks a lot for looking so quickly into this! I've opened an issue with oracle_fdw: https://github.com/laurenz/oracle_fdw/issues/215 Thanks, Chris. --

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.

Re: [GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

Chris Mair writes: > Whenever a session has performed a query on a foreign table, any subsequent > query on a local table big enough to use the parallel query feature exits with > an error: > ERROR: invalid cache ID: 41 > CONTEXT: parallel worker Hm, syscache 41 is

[GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

Hi! I've found a problem in either PostgreSQL 9.6.6 or oracle_fdw 2.0.0. Background: I was testing a setup on a current CentOS 7 system with PostgreSQL 9.6.6 installed from the PGDG repositories and oracle_fdw 2.0.0 installed via pgxn install. Everything went absolutely fine until I enabled

[GENERAL] Query Using Massive Temp Space

Hello, I have a query that is using a tremendous amount of temp disk space given the overall size of the dataset. I'd love for someone to try to explain what PG is doing and why its using so much space for the query. First off, the system is PG 9.6 on Ubuntu with 4 cores and 28 GB of RAM. The

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,

Re: [GENERAL] Query Using Massive Temp Space

Cory Tucker wrote: > I have a query that is using a tremendous amount of temp disk space given the > overall size of the dataset. > I'd love for someone to try to explain what PG is doing and why its using so > much space for the query. It could be a sort or a hash operation. Do determine what

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