postgres cross schema query use UNION ALL

Question

Problem Description：

I have create one database on Postgres, and it has many schemas, such as :

s1,s2,s3, .....   s1000

Each schema has been created one table such as :

CREATE TABLE s1.demos
(
  id bigint NOT NULL DEFAULT s1.next_demos_id(), -- 分片Id
  title character varying(255) NOT NULL, -- 标题
  CONSTRAINT demos_pkey PRIMARY KEY (id)
)
CREATE TABLE s2.demos
(
  id bigint NOT NULL DEFAULT s2.next_demos_id(), -- 分片Id
  title character varying(255) NOT NULL, -- 标题
  CONSTRAINT demos_pkey PRIMARY KEY (id)
)

I have exec the SQL use UNION ALL to select two schemas data, such as:

(SELECT *
 FROM s2.demos
 ORDER BY id ASC
 LIMIT 3)
UNION ALL (SELECT *
           FROM s1.demos
           ORDER BY id ASC
           LIMIT 3)
ORDER BY id DESC

It's result is :

psql (9.5.0)
Type "help" for help.
migrate_development=# (SELECT * FROM s2.demos ORDER BY id ASC LIMIT 3) UNION ALL (SELECT * FROM s1.demos ORDER BY id ASC LIMIT 3) ORDER BY id DESC;
 id |     title
----+----------------
 21 | s2->1 's title
 13 | s1->3 's title
 12 | s1->2 's title
 11 | s1->1 's title
 10 | s2->3 's title
  9 | s2->4 's title
(6 rows)

migrate_development=#

The two demo Tables data such as:

migrate_development=# SELECT * FROM s1.demos;
 id |     title
----+----------------
 11 | s1->1 's title
 12 | s1->2 's title
 13 | s1->3 's title
 14 | s1->4 's title
 15 | s1->5 's title
(5 rows)

migrate_development=# SELECT * FROM s2.demos;
 id |     title
----+----------------
 21 | s2->1 's title
 22 | s2->2 's title
 10 | s2->3 's title
  9 | s2->4 's title
 25 | s2->5 's title
(5 rows)

migrate_development=#

Question

I know i can get all schemas's data by UNION ALL 1000 schemas, but I want to get any other method?

PS. I'm also used Sphinx to solve，But, it's another service，any other agile method？

Answer 1

You can use inheritance postgresql feature

-- create schema for base table and table itself
CREATE SCHEMA s;
CREATE SEQUENCE demos_id_seq START 1;

CREATE TABLE s.demos
(
  id bigint NOT NULL DEFAULT nextval('demos_id_seq'),
  title character varying(255) NOT NULL,
  table_schema text NOT NULL, 
  CONSTRAINT demos_pkey PRIMARY KEY (id)
);

-- create descendant table 1
CREATE SCHEMA s1;
CREATE TABLE s1.demos (
  table_schema text NOT NULL DEFAULT 's1'
) INHERITS (s.demos);

-- create descendant table 1
CREATE SCHEMA s2;
CREATE TABLE s2.demos (
  table_schema text NOT NULL DEFAULT 's2'
) INHERITS (s.demos);

-- create descendant table 1
CREATE SCHEMA s3;
CREATE TABLE s3.demos (
  table_schema text NOT NULL DEFAULT 's3'
) INHERITS (s.demos);

-- can add table to s1000.demos

-- insert some data for test queries
insert into s1.demos (title) VALUES ('s1.1'), ('s1.2'), ('s1.3'), ('s1.4'), ('s1.5');
insert into s2.demos (title) VALUES ('s2.1'), ('s2.2'), ('s2.3'), ('s2.4'), ('s2.5');
insert into s3.demos (title) VALUES ('s3.1'), ('s3.2'), ('s3.3'), ('s3.4'), ('s3.5');

-- query for 3th top row from each descendant table
SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY table_schema ORDER BY id asc) AS r,
    t.*
  FROM
    s.demos t
) x WHERE x.r <= 3;

This query return this output:

     r | id | title | table_schema 
---+----+-------+--------------
 1 |  1 | s1.1  | s1
 2 |  2 | s1.2  | s1
 3 |  3 | s1.3  | s1
 1 |  6 | s2.1  | s2
 2 |  7 | s2.2  | s2
 3 |  8 | s2.3  | s2
 1 | 11 | s3.1  | s3
 2 | 12 | s3.2  | s3
 3 | 13 | s3.3  | s3
(9 rows)