CODEWITHSUNDEEP
postgresqlcommon-table-expression
Nuno Miguel
Nuno Miguel

Reputation: 43

How to return a set of rows of a cte defined within the function?

I have a function that's supposed to return all the children of a specific parent in a table of manager-employee relation.

I have the CTE that defines the recursivity of the problem working, but to allow any parent to be chosen I nested the CTE within a function. It creates the function but when I call it I get the error: query has no destination for result data

CREATE OR REPLACE FUNCTION  display_full_cat(
    PROCURA VARCHAR
)
    RETURNS SETOF (categoria varchar, super_categoria varchar) AS $BODY$
    BEGIN
        WITH RECURSIVE HIERARQUIA AS (
        SELECT C.CATEGORIA, C.SUPER_CATEGORIA
        FROM CONSTITUIDA C
        WHERE C.SUPER_CATEGORIA = PROCURA
        UNION
        SELECT C.CATEGORIA, C.SUPER_CATEGORIA
        FROM CONSTITUIDA C
        INNER JOIN HIERARQUIA H ON H.CATEGORIA = C.SUPER_CATEGORIA
    ) SELECT * FROM HIERARQUIA;

    RETURN QUERY
        SELECT * FROM HIERARQUIA;
    RETURN;

    END
    $BODY$  LANGUAGE PLPGSQL;

NOTE: CONSTITUIDA is the relation that contains the parent-child relation, which, respectively is super_categoria and categoria.

For the data 

super_categoria | categoria
Organism        | Plant
Organism        | Animal
Animal          | Lion
Animal          | Cat
Plant           | Apple
Rock            | Obsidian

The result should be 

super_categoria | categoria
Organism        | Plant
Organism        | Animal
Animal          | Lion
Animal          | Cat
Plant           | Apple

But I instead get the Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function display_full_cat(character varying) line 3 at SQL statement

Upvotes: 0

Views: 2373

Answers (1)

J Spratt
J Spratt

Reputation: 2012

You have RETURN QUERY in the wrong location. It needs to be at the top of the CTE. You're already using SELECT * FROM HIERARQUIA; at the end of the CTE so the final query you're trying to return is duplicate. You're getting the error because of the SELECT at the end of the CTE not being stored or returned anywhere.

CREATE OR REPLACE FUNCTION  display_full_cat(PROCURA VARCHAR)
RETURNS SETOF (categoria varchar, super_categoria varchar) 
AS $BODY$
BEGIN
    RETURN QUERY -- This will return the result from the last query in the CTE.
    WITH RECURSIVE HIERARQUIA 
    AS (
        SELECT C.CATEGORIA, C.SUPER_CATEGORIA
        FROM CONSTITUIDA C
        WHERE C.SUPER_CATEGORIA = PROCURA
        UNION
        SELECT C.CATEGORIA, C.SUPER_CATEGORIA
        FROM CONSTITUIDA C
        JOIN HIERARQUIA H ON H.CATEGORIA = C.SUPER_CATEGORIA
    ) SELECT * FROM HIERARQUIA; -- Prior to moving RETURN QUERY, this was causing the error
END;
$BODY$  LANGUAGE PLPGSQL;

Upvotes: 3

Related Questions