Store select query's output in one array in postgres

Question

My code is

select column_name from information.schema.columns where table_name='aean'

It returns column names of table aean.
Now I have declared an array:

DECLARE colnames text[]

How can I store select's output in colnames array.
Is there any need to initialize colnames?

+1, I reached here with exact same error message- ERROR: could not find array type for data type information_schema.sql_identifier. Was trying with array_agg(column_name, ',')

Denis · Answer 1 · 2011-06-19 13:40:54Z

up vote 5 down vote

There are two ways. One is to aggregate:

select array_agg(column_name::text)
from information.schema.columns
where table_name='aean'

The other is to use the array() operator:

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'
);

edited Jun 19 '11 at 13:40

answered Jun 19 '11 at 11:46

Denis
20.2k11435

	can you give me the code that works on postgres because this is not working on postgres ERROR: could not find array type for data type information_schema.sql_identifier – mitesh Jun 19 '11 at 12:15
	Sorry 'bout that. Mindless copying and pasting got array_agg() included in all three calls. I've also type-casted the initial one to make your PG version happy. – Denis Jun 19 '11 at 13:41
	+1 Thanks, was searching for exact same thing. – jerrymouse Aug 21 '12 at 6:52

1 Answer