Unnesting an array of arrays, to a simple array in plpgsql

Question

This doesn't work:

DO
$do$
    DECLARE
        nested   varchar[][] := '{{a},{b},{c}}';
        unnested varchar[];
    BEGIN    
        unnested := unnest(nested)::text[];
    END
$do$;

Because, it seems, the unnest expression returns a table. The error message is:

[22P02] ERROR: malformed array literal: "a" Detail: Array value must start with "{" or dimension information. Where: SQL statement "SELECT unnest(nested)::text[]" PL/pgSQL function inline_code_block line 7 at assignment

So I guess the solution is to create an array out of the unnest return value? How do I do this?

user330315 · Accepted Answer · 2020-09-29 19:57:08Z

1

You can't cast the result of a set returning function to an array.

DO
$do$
DECLARE
  nested   varchar[][] := '{{a},{b},{c}}';
  unnested varchar[];
BEGIN    
  unnested := array(select * from unnest(nested));
END
$do$;

answered Sep 29, 2020 at 19:57

user330315

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

Collectives™ on Stack Overflow

Unnesting an array of arrays, to a simple array in plpgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related