I'm trying to dynamically create tables and turn their columns into arrays. So I would end up with a table that has just 1 column, of type array. The lenght of the array would be equal to the amount of columns in the input. If I knew in advance which columns my (dynamically created) table has, the solution would be simple, but I don't know this.
I've tried using the array function. This works if you know in advance which columns your table contains. If you don't know this (because the table is dynamically defined) you have to use an asterisk to get all columns. But the array function then creates an array with just 1 element, containing all columns. This is not what I want.
CREATE TEMPORARY TABLE my_addresses(
personname TEXT NOT NULL,
streetname TEXT NOT NULL,
country_id TEXT
);
------------------------------------------------------------------------
INSERT INTO my_addresses
VALUES( 'Bill Gates', 'Somewhere Avenue', 'US'),
('Linus Thorvalds', 'Other Road', 'US'),
('Erwin Brandstetter', 'Solution Street', 'AT'); --it's a tribute :-)
------------------------------------------------------------------------
SELECT
cardinality(ARRAY[ ma.personname,
ma.streetname,
ma.country_id]), --an array of 3 elements is created, as you would expect
cardinality(ARRAY[ma.*]) --an array of 1 element is created, which is not what I want
FROM my_addresses AS ma;
Btw, this is my first post ever on stackoverflow (forgive me any formatting errors). I use Postgres version 9.6.14.
This seems curious. But as with many challenges that involve entire records, the solution often is to use JSON. In this case:
SELECT (SELECT array_agg(t.v)
FROM jsonb_each_text(to_jsonb(ma.*)) t(k, v)
) as myarray
FROM my_addresses ma;
Here is a db<>fiddle.
You may find that JSON is a better solution for your ultimate problem anyway. But this question asks about arrays in particular.