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

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-05-22T03:36:00.547

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?

score 0 · Answer 2 · answered May 20 '23 at 23:18

I found an answer, going in the direction of @erwin

CREATE OR REPLACE FUNCTION get_temps(tid uuid) 
    RETURNS TABLE (temp_id uuid)
    LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN QUERY (
    -- ....
    );
END
$$;

Keys were:

use plpgsql as the language
set the return type to TABLE
use correct syntax for this language type, including begin / return query / end

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

2 Answers2