CODEWITHSUNDEEP
sqlarrayspostgresql
zimon
zimon

Reputation: 171

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

(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.

Upvotes: 1

Views: 1250

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28511

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.

Upvotes: 1

Related Questions