Function returns one row, but the query it wraps returns many

A very odd situation, where a query returns 1380 items, but when wrapped in a function, then same function returns a single row

I've tried deleting and recreating the function.

When called like this, via a function, 1 row is returned.
CREATE OR REPLACE FUNCTION get_temps(tid uuid) RETURNS uuid
LANGUAGE sql
AS
$$
WITH RECURSIVE
start_set (temp_id, display_name, parent_temp_id) AS
(SELECT t.temp_id, t.display_name, t.parent_temp_id
FROM temps t
WHERE t.temp_id = tid),
subitems (temp_id, display_name, parent_temp_id) AS
(SELECT s.temp_id, s.display_name, s.parent_temp_id
FROM start_set s
UNION ALL
SELECT t.temp_id, t.display_name, t.parent_temp_id
FROM temps t
JOIN subitems subs ON t.parent_temp_id = subs.temp_id)
SELECT temp_id
FROM subitems;
$$;


When called not-in-a-function-wrapper, it returns 1380 rows
WITH RECURSIVE
start_set (temp_id, display_name, parent_temp_id) AS
(SELECT t.temp_id, t.display_name, t.parent_temp_id
FROM temps t
WHERE t.temp_id = tid),
subitems (temp_id, display_name, parent_temp_id) AS
(SELECT s.temp_id, s.display_name, s.parent_temp_id
FROM start_set s
UNION ALL
SELECT t.temp_id, t.display_name, t.parent_temp_id
FROM temps t
JOIN subitems subs ON t.parent_temp_id = subs.temp_id)
SELECT temp_id
FROM subitems;


What am I not thinking of?

To return a set of values (or set of rows) it must be a set-returning function:

CREATE OR REPLACE FUNCTION get_temps(tid uuid)
  RETURNS SETOF uuid  -- !!!
  LANGUAGE sql AS
$func$
...
$func$;

Now it can return 0-n rows. That includes returning nothing at all ("no row"). The way you declared it, it's a "scalar" function, always returning a single value. The manual:

The SETOF modifier indicates that the function will return a set of items, rather than a single item.

See (with links to more):

• PL/pgSQL function does not return all the results using record

Alternatively, you can use RETURNS TABLE (...).

Either return type can be used with any LANGUAGE (plpgsql, sql, ...). While you don't need procedural elements provided by PL/pgSQL (or other PL), just pure SQL, it's typically best to stick with plain LANGUAGE sql functions. About the language choice:

• Difference between language sql and language plpgsql in PostgreSQL functions

I might use a "standard SQL" function for your simple case. See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

StackExchange Database Administrators Q#327314, answer score: 2