Combine multiple SELECT statements

Question

I've used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas:

select result from foo.table limit 1;
select result from bar.table limit 1;
select result from doo.table limit 1;

(foo, bar & doo are examples of my schemas, there are hundreds in reality).

Each SELECT will return only one result. I simply want one column result with as many rows as there are schemas. I can then copy this back into Excel against the schema names.

When I run the query above I get 1 row, with the others being discarded:

Query result with 1 row discarded.

Query result with 1 row discarded.

Total query runtime: 40 ms.
1 row retrieved.

I have tried using UNION ALL, but the limit 1 I am using to ensure one row only is returned from each schema table appears to prevent this from working.

How can I either prevent the other rows from being discarded, or write a query that will return the values I need (two columns - schema_name, result - one row for each schema) in a more efficient way?

Answer 1

Wrap individual sub-statements in parenthesis to make the syntax unambiguous:

(SELECT result FROM tbl1 LIMIT 1)
UNION ALL
(SELECT result FROM tbl2 LIMIT 1)

The manual about UNION is very clear on the matter:

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Answer 2

Wrapping in a subquery will get around it, but it gets a bit ugly.

SELECT result FROM (select 'a'::text AS result from foo limit 1) a
UNION ALL
SELECT result FROM (select 'b'::text AS result from bar limit 1) b

UPDATE

See Erwin's response. It is better.