Select Multiple Values into Array and Loop Through – PostgreSQL 9.3

aggregatearraypostgresql

I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a transaction.

CREATE OR REPLACE FUNCTION loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[];
        _id INTEGER;
BEGIN
        SELECT ARRAY(SELECT id FROM loop_test) INTO _ids_array; 
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET looped = TRUE WHERE id = _id;
        END LOOP;
END;
$$ LANGUAGE plpgsql;

Table:

db=# \d loop_test;
      Table "public.loop_test"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 id            | integer | 
 other_id      | integer | 
 id_copy       | integer | 
 other_id_copy | integer | 
 looped        | boolean | 

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | 
  6 |       15 |         |               | 
  2 |       11 |         |               | 
  7 |       16 |         |               | 
  3 |       12 |         |               | 
  4 |       13 |         |               | 
  5 |       14 |         |               | 
(7 rows)

When I call select loop_test(), I get the following results:

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | t
  6 |       15 |         |               | t
  2 |       11 |         |               | t
  7 |       16 |         |               | t
  3 |       12 |         |               | t
  4 |       13 |         |               | t
  5 |       14 |         |               | t
(7 rows)

I would, however, like to create a function to select both the id and the other_id into an array. I was told about using something like agg_array, but I don't completely understand how that works.

I was imagining something like the following?

CREATE OR REPLACE FUNCTION agg_loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[][];
        _id INTEGER;
BEGIN
        SELECT AGG_ARRAY(SELECT id, other_id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET id_copy = _id[0], other_id_copy = _id[1] WHERE id = _id[0];
        END LOOP;
END;
$$ LANGUAGE plpgsql;

Best Answer

A much better way, yet: just update. No loop needed.

UPDATE loop_test
SET    id_copy = id
     , other_id_copy = other_id;
WHERE  id IS NOT NULL;

The WHERE condition is only useful if id can be null and you want a perfect equivalent of what you had.

Loop

If you are just exploring loops - you can assign multiple variables:

CREATE OR REPLACE FUNCTION better_loop_test()
  RETURNS void AS
$func$
DECLARE
   _id int;
   _other_id int;
BEGIN
   -- example makes no sense, just a loop demo
   FOR _id, _other_id IN
      SELECT id, other_id FROM loop_test
   LOOP
      UPDATE loop_test
      SET    id_copy = _id
           , other_id_copy = _other_id
      WHERE id = _id;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

While you just need the two columns of known type, that may be a bit cheaper than fetching whole (possibly big) rows.

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
_{Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!}

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

What is the difference between LATERAL and a subquery in PostgreSQL?

ARRAY constructor in correlated subquery:

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Need to select a JSON array element dynamically from a postgresql table

Notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

PostgreSQL – Reuse SELECT Query by Adding Results in Array

It's a textbook case for a CTE, like @Daniel commented.
The example can be simplified some more. And you need to be aware of how LIMIT works in a UNION query.

CREATE OR REPLACE FUNCTION featured_products(valid_to_in      timestamp
                                           , taxonomy_id_in   integer
                                           , product_limit_in integer)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY
   WITH featured AS (SELECT supplier_id FROM products LIMIT 2)
   SELECT supplier_id
   FROM   featured

   UNION ALL
   (
   SELECT p.supplier_id
   FROM   products p
   LEFT   JOIN featured f USING (supplier_id)
   WHERE  f.supplier_id IS NULL
   LIMIT  product_limit_in
   )  -- parens required - or not?
END
$func$  LANGUAGE plpgsql VOLATILE;

LIMIT can only be applied once in a UNION (ALL) query, unless you enclose the leg of the query in parentheses. You may or may not want to add parentheses.
- The way I have it, a maximum of product_limit_in rows are returned in addition to the "featured" rows from the CTE.
- If you remove the parentheses you get a maximum of product_limit_in rows total - meaning that even "featured" products may be discarded.
  Related: Optimize a query on two big tables
Either way, don't ORDER BY the outer (combined) result before you LIMIT, if you can avoid it. Postgres can optimize the query very efficiently and just stop evaluating once enough rows have been returned (possibly fetching tuples from the top of a matching index). That would not be possible any more, which can make a huge difference in performance.
Using LEFT JOIN / NOT NULL to exclude featured rows from the second SELECT, which is probably faster than NOT IN and does not carry "surprises" when dealing with NULL values or empty results.
- Select rows which are not present in other table
In Postgres (as opposed to some other RDBMS), you can refer to p.supplier_id and f.supplier_id after joining with USING (supplier_id).

And yes, the CTE is only evaluated once:

A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries.

Bold emphasis mine.