PostgreSQL combines several select statements

Question

I used Excel to create numerous SELECT from a list of schema names from a database with lots 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 and doo are examples of my schemes, in fact there are hundreds).

Each SELECT returns only one result. I just need one result column with as many rows as there are schemas. Then I can copy this back to Excel with the schema names.

When I run the query above, I get 1 line and the rest are discarded:

  Query result with 1 row discarded.

 Query result with 1 row discarded.

 Total query runtime: 40 ms.
 1 row retrieved.

I tried to use UNION ALL , but limit 1 , which I use to ensure that one row only comes back from each table of the schema so that this does not work.

How can I either prohibit discarding other rows, or write a query that will return the values ​​I need (two columns - schema_name, the result - one row for each schema)?

Answer 1

Simply enclose individual expressions in parentheses to make the syntax unambiguous:

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

The UNION manual is very important:

select_statement is any SELECT without ORDER BY , LIMIT , FOR UPDATE or FOR SHARE . ( ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these positions will be accepted for application to the UNION result, not its right input expression.)

Answer 2

The wrapper in the subquery will cost, but it's a little 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 answer. It is better.

Answer 3

 create view my_data1 AS with data as ( select student_id,sum(marks) as total_marks from marks_marks group by 1 ) , data1 as ( select id, name from students_class ), data2 as ( select applicant_name, id, class_name from students_students ) select data2.applicant_name , data1.name as class_name , data.total_marks from data2 join data1 on data1.id = data2.class_name join data on data.student_id = data2.id select * from my_data1