Store select query's output in one array in postgres

SqlArraysPostgresqlPlpgsql

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

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::text
FROM information_schema.columns
WHERE table_name='aean'
)

Solution 3 - Sql

CREATE OR REPLACE FUNCTION f_test_array(in _colname text)
returns text as $body$
DECLARE colnames text[];
begin
colnames := ARRAY(
    SELECT column_name FROM information_schema.columns WHERE table_name='customer'
);
    if exists(select _colname = any(colnames))
    then return format('%s it exits.', _colname);
    else return format('%s not exits.', _colname);
end if;
end
$body$
LANGUAGE plpgsql;   

check if the column exists or not. Key point: if exists(select _colname = any(colnames)) We can also using string_agg String_agg usage:

CREATE OR REPLACE FUNCTION f_test_array1(in _colname text)
returns text as $body$
DECLARE colnames text;
begin
colnames := (SELECT string_agg(column_name,',') FROM information_schema.columns WHERE table_name='customer')::text;
if exists(select colnames ilike '%' || quote_literal(_colname) ||'%')
    then return format('column %s  exits.', _colname);
    else return format('column %s does not exits.', _colname);
end if;
end
$body$
LANGUAGE plpgsql;

Solution 4 - Sql

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;

Solution 5 - Sql

Regular:

SELECT post_id FROM posts WHERE(poster_name='John');

output: [
  {'post_id': 1},
  {'post_id': 2},
  {'post_id': 3},
]

Using ARRAY_AGG:

SELECT ARRAY_AGG(post_id) FROM posts WHERE(poster_name='John');

output: [
 {[1, 2, 3]}
]

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionmiteshView Question on Stackoverflow
Solution 1 - SqlDenis de BernardyView Answer on Stackoverflow
Solution 2 - SqlptskiView Answer on Stackoverflow
Solution 3 - SqlMarkView Answer on Stackoverflow
Solution 4 - SqlManish DeshpandeView Answer on Stackoverflow
Solution 5 - SqlAugust KimoView Answer on Stackoverflow