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?

0

2 Answers 2

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:

3
  • 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. May 20, 2023 at 23:26
  • Oh I see what you mean. Just that 'aliasing' it as the start_set or start_temp is unnecesary. Sure / thanks May 20, 2023 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. May 20, 2023 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
2
  • If there is no more than just the one SELECT (with rCTE), a plain LANGUAGE sql function would be just as good. And a bit simpler. May 20, 2023 at 23:37
  • Thanks I clearly have some things to learn about the LANGUAGE options. May 21, 2023 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.