current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
Are you trying to dynamically create a union all query that combines the data from several tables with identical columns? –  Craig Ringer Jul 2 at 13:05
    
@CraigRinger, Yes! You are right. –  Meem Jul 3 at 4:15
    
I shouldn't have to guess that - please try to explain things in questions a bit more. Anyway, I'll get this reopened so I can explain what you're doing wrong and how to do it properly. –  Craig Ringer Jul 3 at 4:36
    
@CraigRinger, Thank you so much. I am sorry for incomplete information. Will take care from next time. –  Meem Jul 3 at 4:39
1  
Well, if you get notified that this gets reopened, message me and I'll follow up. The short version in the mean time: use string_agg to aggregate query strings, using ` UNION ALL ` (not just UNION) as the join string. This sort of thing often suggests an underlying design problem though. –  Craig Ringer Jul 3 at 4:41

1 Answer 1

up vote 2 down vote accepted

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)
)
share|improve this answer
    
Great! Thank you so much. And yes "ROM" was my editing error, I apologies for that. –  Meem Jul 3 at 6:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.