SELECT dynamic columns without functions in PostgreSQL

Question

I need to select rows from two and more tables ("A", "B"). They have differences columns and I don't use inheritance for it.

So. For example:

SELECT * FROM "A" UNION SELECT * FROM "B" 

ERROR: each UNION query must have the same number of columns

I can understand why.

I try get intersected columns from root schema in root table:

SELECT column_name FROM information_schema.columns
WHERE table_schema = 'client_root' AND table_name ='conditions'

It's ok! But I don't use query:

SELECT
   (SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'client_root' AND table_name ='conditions')
FROM "client_123"."A"

So. How I can put sub select data in root select?

Doenitz

Maybe you say to me how to union tables with dymanic structure? – Doenitz

I don't believe there's a way to do this. You'll have to query information_schema.columns to get the necessary metadata for each table. – Acrostic

Answer 1

What you are trying to do is hardly possible in its entirety.

Create dynamic SQL

First, here is what you can do: a plpgsql function that creates the SQL for such a query:

CREATE OR REPLACE FUNCTION f_union_common_col_sql(text, text)
 RETURNS text
AS $function$
DECLARE 
  _cols text;
BEGIN

_cols := string_agg(attname, ', ')
FROM (
    SELECT a.attname
    FROM   pg_attribute a
    WHERE  a.attrelid = $1::regclass::oid
    AND    a.attnum >= 1
    INTERSECT
    SELECT a.attname
    FROM   pg_attribute a
    WHERE  a.attrelid = $2::regclass::oid
    AND    a.attnum >= 1
    ) x;

RETURN 'SELECT ' || _cols || '
FROM   ' || quote_ident($1) || '
UNION
SELECT ' || _cols || '
FROM   ' || quote_ident($1);

END;
$function$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_union_common_col_sql(text, text) IS 'Create SQL to query all visible columns that two tables have in common.
# Without duplicates. Use UNION ALL if you want to include duplicates.
# Depends on visibility dicatated by search_path
$1 .. table1: optionally schema-qualified, case sensitive!
$2 .. table2: optionally schema-qualified, case sensitive!';

Call:

SELECT f_union_common_col_sql('myschema1.tbl1', 'myschema2.tbl2');

Gives you the complete query. Execute it in a second call.

You can find most everything I used here in the manual on plpgsql functions.
The aggregate function string_agg() was introduced with PostgreSQL 9.0. In older versions you would: array_to_string(array_agg(attname), ', ').

---

Execute dynamic SQL?

Next, here is what you hardly can do:

CREATE OR REPLACE FUNCTION f_union_common_col(text, text)
  RETURNS SETOF record AS
$BODY$
DECLARE 
  _cols text;
BEGIN

_cols := string_agg(attname, ', ')
FROM (
    SELECT a.attname
    FROM   pg_attribute a
    WHERE  a.attrelid = $1::regclass::oid
    AND    a.attnum >= 1
    INTERSECT
    SELECT a.attname
    FROM   pg_attribute a
    WHERE  a.attrelid = $2::regclass::oid
    AND    a.attnum >= 1
    ) x;

RETURN QUERY EXECUTE '
SELECT ' || _cols || '
FROM quote_ident($1)
UNION
SELECT ' || _cols || '
FROM quote_ident($2)';

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

COMMENT ON FUNCTION f_union_common_col(text, text) IS 'Query all visible columns that two tables have in common.
# Without duplicates. Use UNION ALL if you want to include duplicates.
# Depends on visibility dicatated by search_path
# !BUT! you need to specify a column definition list for every call. So, hardly useful.
$1 .. table1 (optionally schema-qualified)
$2 .. table1 (optionally schema-qualified)';

A function call requires you to specify the list of target columns. so this is hardly useful at all:

SELECT * from f_union_common_col('myschema1.tbl1', 'myschema2.tbl2')

ERROR:  a column definition list is required for functions returning "record"

There is no easy way around this. You would have to dynamically create a function or at least a complex type. This is where I stop.

Curbing

I asked about the request without creating a new function – Doenitz

@Arturgspb: This is generally impossible in plain SQL. You have to use a function or an anonymous code block (DO statement) for queries involving dynamically determined identifiers. – Curbing

With a large number of appeals that will greatly slow down. – Doenitz

So use two queries. First query the catalog to get the files like I demonstrate in my first function (can be done with plain SQL), build the actual query and execute that. The extra step costs a few milliseconds. – Curbing