3

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
  • 175,982
  • 27
  • 439
  • 600
New Alexandria
  • 137
  • 6

2 Answers2

2

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):

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:

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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 1
    I don't understand the rCTE comment, and am happy to ask it as a new question, if you like. All of the tables are the same table. I should have said sub_temps for clarity. The intent is to find all temps starting with a given parent tid. I could not find a way to refactor this without the temps.temp_id = tid going somewhere that limited all results. – New Alexandria May 20 '23 at 23:26
  • Oh I see what you mean. Just that 'aliasing' it as the start_set or start_temp is unnecesary. Sure / thanks – New Alexandria May 20 '23 at 23:34
  • Let's focus this Q&A on the set-returning nature of functions. I'll remove my aside. (It's also partially off target.) If you suspect room for improvement for the query, start a new question with all relevant details for that. – Erwin Brandstetter May 20 '23 at 23:35
0

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
New Alexandria
  • 137
  • 6