Is it possible to store PostgreSQL (multiple row) query result into an array element?

Question

(Disclaimer: I am quite inexperienced with SQL this far.)

The title already says it. I'd like to have whole tables stored as array elements. So myarray[1] is table, myarray[2] is another table. The tables do have same widths and attributes (column names) but different number of records (rows).

Maybe this is not possible just in PostgreSQL, but should use for example Python to access PostgreSQL database. So maybe, a PosgreSQL array cannot be type 'table', or composite type?

eg. I have a relation (table) with at least two attributes (columns) and several records (rows). The real table has tens of cols and thousands of rows. Greatly simplified:

CREATE TABLE data_orig (category integer, date adate, ...);
INSERT INTO data_orig VALUES (1, 2016-02-28, ...);
INSERT INTO data_orig VALUES (1, 2016-02-29, ...);
-- and so on
INSERT INTO data_orig VALUES (2, 2016-02-28, ...);
INSERT INTO data_orig VALUES (2, 2016-02-29, ...);
INSERT INTO data_orig VALUES (2, 2016-02-27, ...);
--and so on, categories 3 - N

I'd like to have an array, where array elements are results of the queries.

CREATE TYPE mytype AS (
   category int,
   adate date);

-- Pseudo code about definition of the array I'd like to have:

define myarray mytype[];

I'd like to have a loop, where the original data table is pruned from the most recent dates to the oldest. In advance I do not know how many dates in some category there is. So I may end up having 2 or 10 or N tables and therefore array of size 2, 10 or N.

-- Make a copy of the original..

CREATE TEMP TABLE data_temp1 AS 
   SELECT category,adate FROM data_orig;

-- LOOP would be start here. 

-- Get the lines with the latest dates.
CREATE TEMP TABLE data_temp2 AS 
  (SELECT category, max(adate) AS adate 
    FROM data_temp1 GROUP BY category);

-- Store the result to array.
**myarray[1] = data_temp2;**
-- 
-- Substract the last result from the working copy.
CREATE TEMP TABLE data_temp3 AS 
  (SELECT * FROM data_temp1 EXCEPT SELECT * FROM data_temp2);

DROP TABLE data_temp1;
-- Copy the working copy to data_temp1:
CREATE TEMP TABLE data_temp1 AS (SELECT * FROM data_temp3);
DROP TABLE data_temp2;
-- Get the next recent dates:
CREATE TEMP TABLE data_temp2 AS 
  (SELECT category, max(adate) AS adate
    FROM data_temp1 GROUP BY category);

-- Now again, I want to store this data_temp2 table to the array: 
**myarray[2] = data_temp2;**
-- And once again subtract the result data_temp2 from the data_temp1
-- and then repeat, until all rows are inserted in the array of tables

This surely wouldn't be the most efficient, because copying BIG tables several times just for temporary use, but anyway the end result should be to have N tables with decreasing latest dates in them. More relational "left joins" is then done with these tables in the array, the wider data_orig and other tables, so that's why they need to be stored somewhere.

I don't really get your approach but it seems that you overcomplicate the issue. Why do you need arrays anyway? What are you trying to do with the data? There are a lot of CREATE/DROP statements that are not needed. — Kamil Gosciminski
– Kamil Gosciminski, Commented Feb 29, 2016 at 21:13
Trying to extract data to do Kaplan-Meier estimate type of analys of the data (A), which has dates, "categories"=incidents, some incidents have many re-starters, some don't. In another table (B) is then followup data, but there the records have no relation to this start dates table recorded, but has to be searched comparing dates. So table A has start dates and "die" dates, which are also re-start dates. The table B has "still good" dates. — zimon
– zimon, Commented Mar 1, 2016 at 12:23

Ihor Romanchenko · Accepted Answer · 2016-02-29 22:33:50Z

1

You can try something like this:

SELECT category, adate, row_number() OVER (PARTITION BY category ORDER BY adate DESC) as rn
FROM data_orig

This will give each row a number: "1" for the newest row in a category, "2" for the second newest and so on. Something like this:

1, 2016-02-28, 2, ...
1, 2016-02-29, 1, ...

2, 2016-02-28, 2, ...
2, 2016-02-29, 1, ...
2, 2016-02-27, 3, ...

You can use this SELECT as a sub-query in a larger SELECT to build on it.

BTW Do not try to put something in an ARRAY in a relational database. Most often it is not the thing you should do. Arrays belong to programming languages like Java and Python, not SQL. Exceptions to this rule are rare.

answered Feb 29, 2016 at 22:33

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zimon Over a year ago

Thanks. I can use this to do what I intended.

Collectives™ on Stack Overflow

Is it possible to store PostgreSQL (multiple row) query result into an array element?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related