CODEWITHSUNDEEP
postgresqlsubqueryconcatenation
user3523583
user3523583

Reputation: 163

How to use string aggregation on concatenated result subquery in Postgres?

I am trying to use string aggregation on the results of a subquery that contains concatenated results. I would like to create a long text string containing the qualified name "schema.table" for a DB schema I have in Postgres. Do you know how to achieve it? Below is the code I have come up with (not working), it says more than one row returned by a subquery used as an expression.

SELECT string_agg(
    (SELECT CONCAT(table_schema, '.', table_name) table_schema_name
    FROM information_schema.tables
    WHERE table_schema = 'name_of_schema'), ' ')
FROM information_schema.tables
WHERE table_schema = 'name_of_schema'

what I have now is:

| table_schema_name | 

name_of_schema.table1
name_of_schema.table2
name_of_schema.table3

What I would like to achieve is:

|                          agrreagated field                     |

name_of_schema.table1 name_of_schema.table2 name_of_schema.table3

Upvotes: 2

Views: 2077

Answers (2)

DrCabry
DrCabry

Reputation: 66

This query should do the task you're asking for:

SELECT string_agg(CONCAT(table_schema, '.', table_name), ' ')
    FROM information_schema.tables
    WHERE table_schema = 'schema_name'

Upvotes: 2

clemens
clemens

Reputation: 17721

You should either use the query as table expression in your outer select:

SELECT string_agg(s.table_schema_name, ' ') FROM (
    SELECT CONCAT(table_schema, '.', table_name) AS table_schema_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
) s

or simply avoid the sub-select:

SELECT string_agg(CONCAT(table_schema, '.', table_name), ' ')
FROM information_schema.tables
WHERE table_schema = 'public';

Upvotes: 1

Related Questions