On Mon, 2016-04-18 at 11:47 -0700, David G. Johnston wrote: > http://www.postgresql.org/docs/9.4/interactive/plpgsql-control > -structures.html#PLPGSQL-STATEMENTS-RETURNING > > Unlike with SQL language functions you must explicitly return values > from > pl/pgsql function. The documentation describes the various ways to > accomplish this.
Hey David. I think what I'll do is not define the constants using the CONSTANT syntax, but instead have them inline within the SELECT as I had before. Since the query is never seen by a human, there's really no reason to have to beautify it.
On a related note, if I SELECT my_function(123,4) and one of those row IDs doesn't exist in my_table, it should probably error which it doesn't do right now. What would be the most elegant way of handling that scenario?
If you are sticking with pl/pgsql then the most direct solution is to simply:
DECLARE
function_variable type;
BEGIN
SELECT [result]
FROM ... CROSS JOIN ...
INTO STRICT function_variable;
RETURN function_variable;
END;
The STRICT will enforce that exactly one row is returned by the function.
A more user-friendly way would be to *also* do:
PERFORM * FROM tbl WHERE id = a_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Must supply a known a_id'
END IF;
and repeat for the other id.
I say *also* because it is still a good idea to ensure that when you are only expecting a single result row that you are getting a single result row. The STRICT acts like an assertion in that sense - meant for debugging but should never been seen by an end-user unless something is seriously wrong.
If you are going to try and leverage SQL for this now - since you no longer need variables - your options are limited, possibly non-existent within the function itself. Any useful solution is probably worse than just using pl/pgsql. You can force SQL to choke if you see more than one row when only one should be present but it has no qualms seeing an empty set in those same circumstances.