Re: Help with CREATE FUNCTION

On Mon, 2016-04-18 at 17:32 -0700, David G. Johnston wrote:
> If you are sticking with pl/pgsql then the most direct solution is to
> simply:

Hey David.

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

I reckon by now you've successfully convinced me of the merits of
plpgsql over vanilla ANSI sql for this. =) Your solution is very
elegant and functional. Thank you.

--
Kip Warner -- Senior Software Engineer
OpenPGP encrypted/signed mail preferred
http://www.thevertigo.com