CODEWITHSUNDEEP
postgresqlplpgsql
Manolo
Manolo

Reputation: 1676

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

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?

Upvotes: 0

Views: 451

Answers (1)

user330315
user330315

Reputation:

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$;

Upvotes: 1

Related Questions