Left join with dynamic table name derived from column

Question

I am new in PostgreSQL and I wonder if it's possible to use number from table tbc as part of the table name in left join 'pa' || number. So for example if number is 456887 I want left join with table pa456887. Something like this:

SELECT tdc.cpa, substring(tdc.ku,'[0-9]+') AS number, paTab.vym 
FROM public."table_data_C" AS tdc
LEFT JOIN concat('pa' || number) AS paTab ON (paTab.cpa = tdc.cpa)

And I want to use only PostgreSQL, not additional code in PHP for example.

Dewees

That sounds like a terrible data model. You should fix that instead. – Jariah

@a_horse_with_no_name I agree, but what is the fix when you have a requirement like this? – Wertz

Answer 1

Either way, you need dynamic SQL.

Table name as given parameter

CREATE OR REPLACE FUNCTION foo(_number int)
  RETURNS TABLE (cpa int, nr text, vym text) AS  -- adapt to actual data types!
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT t.cpa, substring(t.ku,'[0-9]+'), p.vym 
       FROM   public."table_data_C" t
       LEFT   JOIN %s p USING (cpa)'
     , 'pa' || _number
     );
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM foo(456887)

Generally, you would sanitize table names with format ( %I ) to avoid SQL injection. With just an integer as dynamic input that's not necessary. More details and links in this related answer:
INSERT with dynamic table name in trigger function

Data model

There may be good reasons for the data model. Like partitioning / sharding or separate privileges ...
If you don't have such a good reason, consider consolidating multiple tables with identical schema into one and add the number as column. Then you don't need dynamic SQL.

Consider inheritance. Then you can add a condition on tableoid to only retrieve rows from a given child table:

SELECT * FROM parent_table
WHERE  tableoid = 'pa456887'::regclass

Be aware of limitations for inheritance, though. Related answers:

• Get the name of a row's source table when querying the parent it inherits from
• Select (retrieve) all records from multiple schemas using Postgres

Name of 2nd table depending on value in 1st table

Deriving the name of the join table from values in the first table dynamically complicates things.

For only a few tables

LEFT JOIN each on tableoid. There is only one match per row, so use COALESCE.

SELECT t.*, t.tbl, COALESCE(p1.vym, p2.vym, p3.vym) AS vym
FROM  (
   SELECT cpa, ('pa' || substring(ku,'[0-9]+'))::regclass AS tbl
   FROM   public."table_data_C"
   -- WHERE <some condition>
   ) t
LEFT   JOIN pa456887 p1 ON p1.cpa = t.cpa AND p1.tableoid = t.tbl
LEFT   JOIN pa456888 p2 ON p2.cpa = t.cpa AND p2.tableoid = t.tbl
LEFT   JOIN pa456889 p3 ON p3.cpa = t.cpa AND p3.tableoid = t.tbl

For many tables

Combine a loop with dynamic queries:

CREATE OR REPLACE FUNCTION foo(_number int)
  RETURNS TABLE (cpa int, nr text, vym text) AS
$func$
DECLARE
   _nr text;
BEGIN
FOR _nr IN
   SELECT DISTINCT substring(ku,'[0-9]+')
   FROM   public."table_data_C"
LOOP
   RETURN QUERY EXECUTE format(
      'SELECT t.cpa, _nr, p.vym 
       FROM   public."table_data_C" t
       LEFT   JOIN %I p USING (cpa)
       WHERE  t.ku LIKE (_nr || '%')'
     , 'pa' || _nr
     );
END LOOP;

END
$func$ LANGUAGE plpgsql;

Pyrazole

Thank you very much for your answer. But this number 456887 which I entry to foo() function is number from database (It is this number from first select substring(t.ku,'[0-9]+')). – Dewees

@LubosK.: That makes it more difficult then. I really think your data model should be improved. For now: How many rows do you retrieve at once? And how many different tables do you join to? Can there be multiple distinct values in tdc.ku starting with the same number? – Pyrazole

@LubosK.: I added two solutions that should cover all cases. – Pyrazole