Store select query's output in one array in postgres

sql arrays postgresql plpgsql
mitesh picture mitesh · Jun 19, 2011 · Viewed 111.6k times · Source

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?

Answer

Denis de Bernardy picture Denis de Bernardy · Jun 19, 2011

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

Related questions

Check if value exists in Postgres array

Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this: select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int) But I keep thinking there …

Read More
PostgreSQL unnest() with element number

When I have a column with separated values, I can use the unnest() function: myTable id | elements ---+------------ 1 |ab,cd,efg,hi 2 |jk,lm,no,pq 3 |rstuv,wxyz select id, unnest(string_to_array(elements, ',')) AS elem …

Read More
How to find the size of an array in postgresql

Is there any way to find a size of an array? For Example, CREATE TABLE example (id integer[]) ; INSERT INTO exam VALUES ( '{}'); INSERT INTO exam VALUES ( '{5,6,7}'); From this, is there any possibilities to get a result …

Read More