PostgreSQL – Creating View of Multiple Tables in Function

postgresql

I want to create view of multiple table in function using PostgreSQL 9.3 version.

Example:

I have following tables in the table name "AllTables":

table1, table2,table3

And I have following tables in the database:

table4,table5

Now I need to combine these tables into one and create view within a function.

create or replace function fun1()
returns void as 
$body$
Declare
       que varchar;
       b varchar;
Begin
      Select 'select * from ROM ' ||  quote_ident("TableName") ||' UNION'
    FROM "AllTables" a
    INNER JOIN INFORMATION_SCHEMA.Tables so on a."TableName" = so.Table_name into b;

    que := 'Create view view1 as '|| LEFT(b,length(b)-length('UNION'));  
    raise notice '%',que;
    execute que;
end;
$body$
language plpgsql;

Note: Able to get only one table. Not getting all tables view.

Best Answer

What you need to do is:

  • Build each individual query; then
  • string_agg them together with UNION ALL as the joining string.

UNION ALL is important, not just UNION, as UNION may force a sort-and-de-duplicate operation, which is slow.

I will use the format function and its %I and %L format-specififers instead of quote_ident and quote_literal respectively, because it's massively more readable that way.

I have no idea what the stray ROM in:

'select * from ROM ' ||  quote_ident("TableName") ||' UNION'

is. It doesn't make sense. I'm ignoring it. I assume it's probably an editing error, the end of an accidentally remaining FROM.

So, first, take a query that produces a list of queries for each table:

SELECT
   format('SELECT * FROM %I', "TableName")
FROM "AllTables" a
INNER JOIN INFORMATION_SCHEMA.TABLES so ON a."TableName" = so.TABLE_NAME;

then wrap it in an aggregate (string_agg) to combine the strings, prefix the CREATE VIEW and store the result in your query variable and execute it:

SELECT INTO que
   'CREATE VIEW view1 AS ' ||
   string_agg( format('SELECT * FROM %I', "TableName"), ' UNION ALL ')
FROM "AllTables" a
INNER JOIN INFORMATION_SCHEMA.TABLES so ON a."TableName" = so.TABLE_NAME;

raise notice '%',que;
execute que;

BTW, needing to do things like this often means you have a bit of a database design issue. You should not need tons of little tables with the same schema like this. It usually means you're doing this:

CREATE TABLE customer01_transactions (
    trans_id serial primary key,
    ...
)

CREATE TABLE customer02_transactions (
    trans_id serial primary key,
    ...
)

when you should usually be doing this:

CREATE TABLE customer_transactions (
    customer_id integer,
    trans_id serial,
    ...,
    PRIMARY KEY(customer_id, trans_id)
)
Related Question