Function to select from table2 if no record found in table1?

Question

I would like to write a function/procedure in Postgres with 2 SELECT statements. First I select from table1 and if there is no record matching in table1 then perform the same SELECT on table2. E.g.

function getRecord(String: inValue){
  If (select col1, col2 from table1 where col3=inValue) then
    return the row (with col1 and col2);
  else
    return select col1, col2 from TABLE2 where col3=inValue;
}

Is it possible to have a function like this in PostgreSQL (ver 9.1 and above)?

Answer 1

You probably don't need a function, just the query. But here goes:

PL/pgSQL function

CREATE OR REPLACE FUNCTION get_record(_value text)
  RETURNS TABLE(col1 int, col2 text) AS  -- replace with actual columns / types
$func$
BEGIN

RETURN QUERY
SELECT col1, col2 FROM table1 WHERE col3 = _value;

IF NOT FOUND THEN
   SELECT col1, col2 FROM table2 WHERE col3 = _value;
END IF;

END
$func$ LANGUAGE plpgsql;

• You can use the special variable FOUND to check.

• This can return any number of rows, the second query is called if the first does not return anything.

SQL function

CREATE OR REPLACE FUNCTION get_record(_value text)
  RETURNS TABLE(col1 int, col2 text) AS  -- replace with actual columns / types
$func$

SELECT col1, col2 FROM table1 WHERE col3 = _value
UNION ALL
SELECT col1, col2 FROM table2 WHERE col3 = _value
LIMIT  1;

$func$ LANGUAGE sql;

• If the parameter is on a unique column and a single row is guaranteed.

• In older versions you need to use positional references for parameters in SQL functions. i.e.: $1 instead of _value in the body. (You can still name the parameter in the function header, which is useful for documentation and calling with named parameters.)

• Without parentheses the LIMIT 1 applies to the whole of the query, not just the last leg. Without ORDER BY! in the outer query Postgres calls each SELECT until enough rows are returned to satisfy the LIMIT clause. If you run ANALYZE for the query you will see "never executed" for remaining legs of the UNION query.

Related answers:

• Way to try multiple SELECTs till a result is available?

• PostgreSQL combine multiple select statements

Answer 2

Might be able to do it with a query like this:

SELECT COALESCE(a.col1, b.col1) AS col1
      ,COALESCE(a.col2, b.col2) AS col2
  FROM table1 a
  LEFT OUTER JOIN table2 AS b ON (a.col3 = inValue AND b.col3 = a.col3)

This assumes col1 and col2 are not nullable. Might also be able to approach it with something like this:

SELECT col1, col2
  FROM ( SELECT col1, col2, 1 AS ordering
           FROM table1
           WHERE col3 = inValue
         UNION
         SELECT col1, col2, 2 AS ordering
           FROM table2
           WHERE col3 = inValue
       ) x
   ORDER BY ordering
   LIMIT 1;

If you really want to do it in a stored procedure, you can SELECT ... INTO local variables from table1 and then check IF NOT FOUND THEN to see if you need to query the other table. Then return the row created from the local variables. See example mid-way down this page http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html.