CODEWITHSUNDEEP
postgresqlmultidimensional-array
Guest Posting
Guest Posting

Reputation: 395

PostgreSQL - SELECT INTO ARRAY

I've been scouring this site to find out how to dump the results of a select statement to an array, so I can iterate through an array. However I have not been lucky enough to find a simple example. The code below is an example of what I did. However I cannot figure out how to do this with an array. Only a row construct. Whenever I attempt to assign the query results to an array I get this subquery error (i.e. something like "array := (select...from sometable)", which I understand, but there has to be a way to do it. Thanks in advance. (Addition: PSeudo-Code for what I prefer beneath this actual code).

DO
$$
DECLARE
    nRowCount          bigint;
    i                  record;
BEGIN

DROP TABLE IF EXISTS companies_sample_db_temp;
CREATE TABLE companies_sample_db_temp (
col1     varchar
, col2     varchar
, col3     varchar
);

INSERT INTO companies_sample_db_temp VALUES ('McDonalds','Los Angeles','CA');
INSERT INTO companies_sample_db_temp VALUES ('Starbucks','Seattle','WA');
INSERT INTO companies_sample_db_temp VALUES ('Oracle','San Francisco','CA');

-- SELECT * FROM companies_sample_db_temp;

FOR i IN
  with a as
  (
    SELECT
    ARRAY[col1::text
          , col2::text
          , col3::text                    
    ] as coltext
    FROM companies_sample_db_temp AS my_arr
  )
  select row_number() over(), coltext from a
LOOP
--    RAISE INFO 'nRowCount: %', nRowCount;
     RAISE INFO 'Array Info: %', i.coltext[1];
END LOOP;

END
$$;

/*********** Pseudo Code of what I'd rather do *******************/
DO
$$
DECLARE
    -- Assign results of this query to an array
    my_arr := SELECT col1, col2,col3 FROM companies_sample_db_temp;
    i                  record;

BEGIN
-- Loop through an "array" not a table using a select statement.
FOR i IN
    -- Iterate through each row of the array
    my_arr[i] -- Row from the select query
    LOOP
       -- Display an elements within a single array row
       RAISE INFO 'Array Info: %', my_arr[i][1]; -- col1
       RAISE INFO 'Array Info: %', my_arr[i][2]; -- col2
       RAISE INFO 'Array Info: %', my_arr[i][3]; -- col3
END LOOP;
END $$;

Hope this clears up the question.

Upvotes: 8

Views: 13111

Answers (1)

IMSoP
IMSoP

Reputation: 97968

Given that your reasoning for attempting this is "performance", consider the following:

  1. Tables and rows (or, in non-SQL terms, relations and tuples) are Postgres's bread and butter; if they weren't efficient, it would be a pretty poor DBMS.
  2. "Premature optimisation" is generally counter-productive: the more time you spend early on things you think might have performance impact, the less time you have later to fix things which do have performance impact. If you're encapsulating a process in a function, that's a pretty good place to emphasise simplicity and maintainability, because you can substitute a new implementation later if you find it is actually giving you problems.
  3. It's pretty rare for a DBMS to even have array types, and any problem that seems to need loops in a database function can usually be re-framed as an operation against a set of data, and the solution turned into SQL proper. The DBMS might decide to use a loop internally, but 99% of the time, it's better at making that decision than you.
  4. You mention "memory variables"; I'm not quite sure what you mean by that (everything's in memory when it's being operated on), but it sounds like you're making quite in-depth assumptions about how different data types will work "under the hood". A dynamic array and a set of records are both complex data structures which will need to be managed; there's no fundamental reason in any language why one would be more efficient, except that languages tend to be optimised for certain cases. (And thus see point 1.)

Upvotes: 2

Related Questions