Store select query's output in one array in postgres
Solution 1
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
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
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]}
]
Solution 4
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 5
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;
Related videos on Youtube
mitesh
Updated on July 08, 2022Comments
-
mitesh almost 2 years
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?-
jerrymouse almost 12 years+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, ',')
-
Aizhan Nessipbayeva about 5 years
-
-
mitesh almost 13 yearscan 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
-
Denis de Bernardy almost 13 yearsSorry '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.
-
user2490003 almost 6 yearsFor anyone looking further on the plpgsql part you can
DECLARE
an array asmy_array INTEGER[];
(or whatever the relevant type is). You can also use the array in a query'sWHERE
clause likeWHERE values = ANY(my_array)
. TheANY
takes an array or a set and will check for presence in that array/set, so it functions equivalently toIN ()
in a sense -
Sami Ahmed Siddiqui about 5 yearsThis doesn't appear to be the case anymore.
-
Deven T. Corzine over 2 yearsThe examples above should be
information_schema
, notinformation.schema
.