Convert table into array of custom types

Question

it's easy to convert one column table to single dimension array;

my_array integer[];
my_array := ARRAY(SELECT * FROM single_column_table);

But in my case, I need to convert table with several columns to array of custom type objects;

So I have custom type

TYPE dbfile AS
   (fileid integer,
    deleted boolean,
    name text,
    parentid integer,
    ...
ALTER TYPE dbfile

and array declared as

my_files dbfile[];

-- how to cast table to array of custom types???
my_files := SELECT * FROM get_files();  -- get_files return SETOF dbfile.

how to cast table to array of custom types?

ARRAY() does not work, as it requires single column.

Pavel Stehule · Accepted Answer · 2015-09-18 08:40:56Z

You have to use a ROW constructor:

postgres=# SELECT * FROM foo;
┌────┬───────┐
│ a  │   b   │
╞════╪═══════╡
│ 10 │ Hi    │
│ 20 │ Hello │
└────┴───────┘
(2 rows)

postgres=# SELECT ARRAY(SELECT ROW(a,b) FROM foo);
┌──────────────────────────┐
│          array           │
╞══════════════════════════╡
│ {"(10,Hi)","(20,Hello)"} │
└──────────────────────────┘
(1 row)

Any PostgreSQL table has virtual column named as table of record type with with fields related to table's columns. You can use it:

postgres=# SELECT ARRAY(SELECT foo FROM foo);
┌──────────────────────────┐
│          array           │
╞══════════════════════════╡
│ {"(10,Hi)","(20,Hello)"} │
└──────────────────────────┘
(1 row)

Collectives™ on Stack Overflow

Convert table into array of custom types

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