CODEWITHSUNDEEP
sqlpostgresqlselectprocedure
user1085687
user1085687

Reputation: 631

Display multiple queries with different row types as one result

In PostgreSQL 8.3 on Ubuntu, I do have 3 tables, say T1, T2, T3, of different schemas. Each of them contains (a few) records related to the object of the ID I know. Using 'psql', I frequently do the 3 operations:

SELECT field-set1 FROM T1 WHERE ID='abc';

SELECT field-set2 FROM T2 WHERE ID='abc';

SELECT field-set3 FROM T3 WHERE ID='abc';

and just watch the results; for me it is enough to see.

Is it possible to have a procedure/function/macro etc, with one parameter 'id', just running the three SELECTS one after another, displaying results on the screen ?

field-set1, field-set2 and field-set 3 are completely different.

There is no reasonable way to JOIN the tables T1, T2, T3; these are unrelated data. I do not want JOIN. I want to see the three resulting sets on the screen.

Any hint?

Upvotes: 0

Views: 3653

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

Quick and dirty method

If the row types (data types of all columns in sequence) don't match, UNION will fail.
However, in PostgreSQL you can cast a whole row to its text representation:

SELECT t1:text AS whole_row_in_text_representation FROM t1 WHERE id = 'abc'

UNION ALL
SELECT t2::text FROM t2 WHERE id = 'abc'

UNION ALL
SELECT t3::text FROM t3 WHERE id = 'abc';

Only one ; at the end, and the one is optional with a single statement.

A more refined alternative

But also needs a lot more code. Pick the table with the most columns first, cast every individual column to text and give it a generic name. Add NULL values for the other tables with fewer columns. You can even insert headers between the tables:

SELECT '-t1-'::text AS c1, '---'::text AS c2, '---'::text AS c1  -- table t1
UNION ALL
SELECT '-col1-'::text, '-col2-'::text, '-col3-'::text  -- 3 columns
UNION ALL
SELECT col1::text, col2::text, col3::text FROM t1 WHERE id = 'abc'

UNION ALL
SELECT '-t2-'::text, '---'::text, '---'::text  -- table t2
UNION ALL
SELECT '-col_a-'::text, '-col_b-'::text, NULL::text  -- 2 columns, 1 NULL
UNION ALL
SELECT col_a::text, col_b::text, NULL::text FROM t2 WHERE id = 'abc'

...

Upvotes: 2

juergen d
juergen d

Reputation: 204854

put a union all in between and name all columns equal

 SELECT field-set1 as fieldset FROM T1 WHERE ID='abc';
 union all
 SELECT field-set2 as fieldset FROM T2 WHERE ID='abc';
 union all
 SELECT field-set3 as fieldset FROM T3 WHERE ID='abc';

and execute it at once.

Upvotes: 1

Related Questions