Re: Help with CREATE FUNCTION

On Mon, Apr 18, 2016 at 5:18 PM, Kip Warner <kip(at)thevertigo(dot)com> wrote:

> 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.

David J.
​