CODEWITHSUNDEEP
sqlpostgresqlplpgsql
h1ka
h1ka

Reputation: 1

Select from all tables inside the schema containing column with name

How can I get select (table_name, table_name.age)? I need to get values from column 'age' from all tables having this column/

I have this function

CREATE OR REPLACE FUNCTION union_all_tables()
 RETURNS TABLE
  (
    age   bigint
  ) AS
$$
DECLARE
 dynamic_query text = '';
 r_row         record;
BEGIN
FOR r_row IN SELECT table_schema || '.' || table_name qualified_table_name
       FROM information_schema.COLUMNS
       WHERE column_name = 'age' 
LOOP
dynamic_query := dynamic_query || format('UNION SELECT ' ||                                      
                                       'age ' ||
                                       'FROM %s ',r_row.qualified_table_name) || E'\n'; -- adding new line for pretty print, it    is not necessary
END LOOP;

dynamic_query := SUBSTRING(dynamic_query, 7) || ';';

RAISE NOTICE 'Union all tables in staging, executing statement: %',    dynamic_query;
RETURN QUERY EXECUTE dynamic_query;
END;
$$
LANGUAGE plpgsql;

Upvotes: 0

Views: 94

Answers (1)

user330315
user330315

Reputation:

You don't need to generate a single huge UNION statement. If you use RETURN QUERY the result of that query is appended to the overall result of the function every time you use it.

When dealing with dynamic SQL you should also use format() to properly deal with identifiers.

Your function can be simplified to:

CREATE OR REPLACE FUNCTION union_all_tables()
 RETURNS TABLE (table_schema text, table_name text, age bigint) 
AS
$$
DECLARE
 dynamic_query text = '';
 r_row         record;
BEGIN
  FOR r_row IN SELECT c.table_schema, c.table_name
               FROM information_schema.columns c
               WHERE c.column_name = 'age' 
  LOOP
    dynamic_query := format(
                      'select %L as table_schema, %L as table_name, age from %I.%I', 
                           r_row.table_schema, r_row.table_name, 
                           r_row.table_schema, r_row.table_name);
    RETURN QUERY EXECUTE dynamic_query;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

Note that the whole function will fail if there is (at least) one table where the age column is not a bigint.

Upvotes: 1

Related Questions