The system tables are the implementation of database objects. So if you, say, call a function foo()
, the DBMS looks in pg_proc
to see if there is a function foo
and what the arguments and the source code and so on are. The layout and arrangement of the system catalogs are merely the way the implementors of various features over time made them. You already pointed out the documentation of the catalogs. In many cases, there is a simple mapping, say, between a function and pg_proc
. But in other cases, such as for an index, it's a bit more complicated. You will have to dig that information out of the documentation or perhaps the many examples of system catalog queries flying around.
The information schema is specified by the SQL standard. The principle there is, if you enter these DDL commands, then a query of the information schema should give these results. In many cases, there is again a simple mapping between objects and information schema views, but it's not straightforward in all cases. So maintaining a separate documentation of the mapping of this information would be cumbersome and probably useless. The principle is DDL in, information schema out, not what happens in the PostgreSQL system catalogs.
Ultimately, if you want to know whether a sequence would be found in a catalog table or an information schema view, you need to query these tables. The reality is too complicated for this to be simpler. (I think. Send a patch if you have a better idea.)
You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):
SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
select CONCAT(table_schema,'.',table_name) tb
from information_schema.tables
where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
First, let's make some sample tables with sample data:
DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
foo_id int,
bar_id int,
bar_weight int,
primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Here are the sample tables being made and loaded:
mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)
mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)
mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)
mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
-> (
-> foo_id int,
-> bar_id int,
-> bar_weight int,
-> primary key (foo_id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
Now let's build the query and execute it:
mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
-> GROUP_CONCAT(tb SEPARATOR
-> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
-> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
-> INTO @foo_query FROM
-> (
-> select CONCAT(table_schema,'.',table_name) tb
-> from information_schema.tables
-> where table_name like 'foo_reference%'
-> ) A;
Query OK, 1 row affected (0.02 sec)
mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)
mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
| 27 | 90 |
| 47 | 70 |
| 67 | 50 |
| 37 | 40 |
| 57 | 20 |
+--------+------------+
5 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
CAVEAT
Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.
Also, notice that the subquery will get 6 rows, and the limit 0,5
properly displays the first 5 rows of the subquery.
Give it a Try !!!
Best Answer
@ypercube's solution (in the comments above):
worked perfectly. I'm writing it up here so that I can accept the answer.