Postgresql Using the Result set as values for querying

Question

What I am trying to do is to aggregate all data from all of my tenants/users and from there make an SQL view. However the creation of Tenants is created dynamically but rest assured, all of their tables are the same.

What I am trying to do here is first get all the tenants in my database using this query.

select distinct table_schema 
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema','public')
and table_schema not like 'pg_toast%'

What I want to do is for each row, I'll do a union.

something like this

(SELECT * FROM tenant1.ref_product)
UNION ALL(SELECT * FROM tenant2.ref_product) 
UNION ALL(SELECT * FROM tenant3.ref_product)

is this possible?

I hope table_schema will returns tenant1,tenant2,tenant3,right ?? — Vivek S.
– Vivek S., Commented Apr 2, 2015 at 9:36
just show table ref_product's structure(create script) ??? — Vivek S.
– Vivek S., Commented Apr 2, 2015 at 9:51
But the tables are automatically generated by our ORM. no script. and your comment is out of the question. I was looking for answer that will allow me to loop the result set. — user962206
– user962206, Commented Apr 2, 2015 at 9:54

MatheusOl · Accepted Answer · 2015-04-04 14:56:47Z

2

You can't do that in one command, you need first to execute one to build the SELECT statement you want, and the copy its result and execute it:

SELECT string_agg(
    format('(SELECT * FROM %I.ref_product)', nspname),
           ' UNION ALL ')
FROM pg_namespace
WHERE nspname !~ '^(pg_.*|information_schema|public)$';

Another option is to use PL/pgSQL EXECUTE command, for example:

CREATE OR REPLACE FUNCTION ref_product()
RETURNS SETOF tenant1.ref_product
LANGUAGE plpgsql STABLE AS $$
DECLARE
    v_cmd text;
BEGIN
    SELECT string_agg(
        format('(SELECT * FROM %I.ref_product)', nspname),
               ' UNION ALL ')
    INTO v_cmd
    FROM pg_namespace
    WHERE nspname !~ '^(pg_.*|information_schema|public)$'
    RETURN QUERY EXECUTE v_cmd;
END;
$$;

At last, if you are using this often, consider create another schema (let's say "tenant_all") with the same table definition, and set all other table as child of it using PostgreSQL's INHERTIS. For example:

CREATE SCHEMA tenant_all;
CREATE TABLE tenant_all.ref_product(LIKE tenant1.ref_product);
ALTER TABLE tenant1.ref_product INHERIT (tenant_all.ref_product);
ALTER TABLE tenant2.ref_product INHERIT (tenant_all.ref_product);
ALTER TABLE tenant3.ref_product INHERIT (tenant_all.ref_product);
...
ALTER TABLE tenantN.ref_product INHERIT (tenant_all.ref_product);

So that way you can simple query tennat_all.ref_product and it will work exactly as if you are querying it one appended with UNION ALL.

Of course in all cases I'm assuming the tables schema matches perfectly.

answered Apr 4, 2015 at 14:56

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user962206 Over a year ago

On your third example. if there's new a product at tenant3. Will it immediately reflect tenant_all?

user962206 Over a year ago

do the updates cascade? What I am trying to do is aggregate all data from different tenants into one view/source

MatheusOl Over a year ago

@user962206, as I said it will work exactly as querying each one with UNION ALL. That is how inheritance work in PostgreSQL. When you query the parent (e.g. FROM master) is like having it and all children with appended (e.g. FROM (SELECT * FROM ONLY(master) UNION ALL SELECT * FROM child1 UNION ALL SELECT * FROM child2 ...) AS master). So yes, any modification on "tenant*" will be visible by querying "tenant_all". Just make sure to do DDL changes on the "tenant_all" only, or at least to keep the changes in sync.

user962206 Over a year ago

I tried running your command, the one that uses inheritance, it only displayed CREATE TABLE tenant_all.ref_product(LIKE tenant.ref_product..) does not exist even if tenant1 exists,

MatheusOl Over a year ago

@user962206, I'm guessing you meant LIKE tenant1.ref_product ..., see tenant1 instead of tenant

Collectives™ on Stack Overflow

Postgresql Using the Result set as values for querying

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related