[sql] Store select query's output in one array in postgres

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?

This question is related to sql arrays postgresql plpgsql

The answer is


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

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to arrays

PHP array value passes to next row Use NSInteger as array index How do I show a message in the foreach loop? Objects are not valid as a React child. If you meant to render a collection of children, use an array instead Iterating over arrays in Python 3 Best way to "push" into C# array Sort Array of object by object field in Angular 6 Checking for duplicate strings in JavaScript array what does numpy ndarray shape do? How to round a numpy array?

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to plpgsql

No function matches the given name and argument types Postgres FOR LOOP Return zero if no record is found PostgreSQL - SQL state: 42601 syntax error Store query result in a variable using in PL/pgSQL PL/pgSQL checking if a row exists PostgreSQL IF statement Postgresql, update if row with some unique value exists, else insert Loop over array dimension in plpgsql How to return result of a SELECT inside a function in PostgreSQL?