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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With