Throw error for multiple results in PostgreSQL SQL function

Question

Consider the following example function:

CREATE FUNCTION test()
  RETURNS INTEGER
  LANGUAGE SQL
  AS $$ SELECT * FROM UNNEST(ARRAY[1,2,3,4,5]); $$
;

When we execute it like so:

SELECT test();

the result we get back is 1. In other words, the function just returned the first element of the result set.

I have a convenience function that retrieves an ID based on data in the rest of the row. It lets callers query on data that is often unique to a particular row but is not strictly unique. Primarily, it will be used to populate some mostly static data. Allowing the call to use the row data instead of hard coding the ID makes it more readable and easier to maintain when new data does come along. This depends on the caller having some familiarity with the data, but that's a reasonable assumption to make for the usage at hand.

The problem is if the user makes a mistake and gives arguments that are insufficient to filter the query's results to one row, I want my function to error out instead of returning the first result. How can I accomplish this? Do I have any options other than to switch my language to PL/PGSQL and check it manually?

(Using PostgreSQL 9.3).

Answer 1

You could put the entire query as a subquery in the SELECT list. When attempting to return more than one row, this will fail with this error:

ERROR: more than one row returned by a subquery used as an expression

Based on your example, this one would fail:

CREATE FUNCTION test()
  RETURNS INTEGER
  LANGUAGE SQL
  AS $$ SELECT (SELECT * FROM UNNEST(ARRAY[1,2,3,4,5])); $$

and this one wouldn't fail:

CREATE FUNCTION test()
  RETURNS INTEGER
  LANGUAGE SQL
  AS $$ SELECT (SELECT * FROM UNNEST(ARRAY[1])); $$