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)?
You probably don't need a function, just the query. But here goes:
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.
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:
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.
