Store select query's output in one array in postgres
There are two ways. One is to aggregate:
SELECT array_agg(column_name::TEXT)FROM information.schema.columnsWHERE table_name = 'aean'
The other is to use an array constructor:
SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name = 'aean')
I'm presuming this is for plpgsql. In that case you can assign it like this:
colnames := ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name='aean');
I had exactly the same problem. Just one more working modification of the solution given by Denis (the type must be specified):
SELECT ARRAY(SELECT column_name::textFROM information_schema.columnsWHERE table_name='aean')
Casting to the datatype "TEXT" will ensure that your queries will run without any problem.In plpgsql when we assign to a array variable, we need not use the type casting. My requirement was to get a CSV of all the column names of a particular table. I'd used the following code in plpgsql.
Declare col_list varchar[]:=NULL;cols varchar:=NULL;Begin col_list := ARRAY(select t.name from frm_columns t where t.tname='emp_mstr'); cols := array_to_string(col_list,','); return cols;End;