Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to join table with dynamic identifier in postgres?

I have a table name table containing two columns foreign_table_name, and foreign_key.

Is it possible to write a SELECT query that would JOIN values of this table and the table which name is specified in the column foreign_table_name ?

For instance, if we know that all possible targetted foreign tables have a name field, I would like to know if I could write something that would:

SELECT table.foo, table.bar, foreign_table.name 
FROM table
  JOIN $foreign_table AS foreign_table 
       ON (foreign_table.id = table.foreign_key
           $foreign_table = table.foreign_table);

Any solution using PlpgSQL is of course accepted.

Here's a simple content:

Table ``table``
------------------------------------------------
| foo | bar | foreign_table_name | foreign_key |
------------------------------------------------
|  A  |  1  | fruits             | 8           |
|  B  |  2  | vegetable          | 5           |
------------------------------------------------

Table ``fruit``
---------------
| id  | name  |
---------------
| 8   | apple |
---------------

Table ``vegetable``
----------------
| id  | name   |
----------------
| 5   | carrot |
----------------

The expected result table would be:

----------------------
| foo | bar | name   |
----------------------
|  A  |  1  | apple  |
|  B  |  2  | carrot |
----------------------

EDIT: I added the full table example in an attempt to be clearer.

like image 540
vaab Avatar asked Feb 07 '14 04:02

vaab


People also ask

What is dynamic query in PostgreSQL?

Dynamic SQL is used to reduce repetitive tasks when it comes to querying. For example, one could use dynamic SQL to create table partitioning for a certain table on a daily basis, to add missing indexes on all foreign keys, or add data auditing capabilities to a certain table without major coding effects.

Is dynamic query is supported in PostgreSQL?

USING only works in PL/PgSQL - ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.


1 Answers

It's usually way easier to do this sort of thing on the client side, but if you want it's possible with PL/PgSQL, e.g.

CREATE OR REPLACE FUNCTION dynamic_call(tblname text)
RETURNS TABLE (foo int, bar text, fname text)
AS $$
BEGIN
  RETURN QUERY EXECUTE format('
    SELECT t.foo, table.bar, f."name"
    FROM mytable t
    JOIN %I AS f ON (f.id = t.foreign_key);', tblname);
END;
$$ LANGUAGE plpgsql;

For more information, see the PL/PgSQL documentation.

like image 62
Craig Ringer Avatar answered Oct 16 '22 00:10

Craig Ringer