Search code examples
postgresqlplpgsqldatabase-schemadynamic-sqlsql-function

How to return diferent table data based on an ID passed to an SQL function

Based on this question I would like to know if it is possible to return different table data based on an ID passed to the function. Something like (pseudocode): 

CREATE FUNCTION schemaB.testFunc(p_id INT, select_param INT)
  RETURNS setof  schemaZ.Table_1
AS
$$
CASE
    WHEN select_param = 1 THEN SELECT * FROM schemaZ.Table_1 WHERE id = p_id
    WHEN select_param = 2 THEN SELECT * FROM schemaZ.Table_2 WHERE id = p_id
END;
$$
language sql;

Table_1 and Table_2 share no same columns and that invalidates the above RETURNS clause.

Solution

  • This is generally impossible with SQL functions. Even with a polymorphic return type, the actual return type must be determined at call time. But all statements in an SQL function are planned before the function is executed. So you'd always end up with an error message for one of the SELECT statements returning data that doesn't fit the return type.

    The same can be done with dynamic SQL in a PL/pgSQL function - with some trickery:

    CREATE OR REPLACE FUNCTION f_demo(_tabletype anyelement, _id int)
  RETURNS SETOF anyelement LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
      format('SELECT * FROM %s WHERE id = $1', pg_typeof(_tabletype))
   USING  _id;
END
$func$;

    Call (important!):

    SELECT * FROM f_demo(null::schemaZ.Table_1, 1);

    The "trick" is to cast a null value to the desired table type, thereby defining the return type and choosing from which table to select. Detailed explanation:

    Take this as proof of concept. Typically, there are better (safer, less confusing, more performant) solutions ...

    Related: