I have a problem using WITH clause and "RETURN QUERY SELECT" together. I tried the following "simplified" function, but got an error. How can I return result-set from WITH clause statement?

CREATE FUNCTION f1() RETURNS TABLE(v1 varchar, v2 varchar)
LANGUAGE plpgsql
AS $$                 
BEGIN   
    WITH tbl AS 
    (
        SELECT 'a' AS aa , 'b' AS bb     
    )
    RETURN QUERY SELECT aa as v1, bb as v2 from tbl
END;   
$$;



ERROR:  syntax error at or near "RETURN"
LINE 10:     RETURN QUERY SELECT aa as v1, bb as v2 from tbl
             ^
SQL state: 42601
Character: 215

CodePudding user response：

RETURN QUERY comes before with as a CTE is part of the query.
A semicolon is missing after the end of the query.
Unrelated but better use text data type. Here is the same function corrected.

CREATE OR REPLACE FUNCTION f1()
RETURNS TABLE(v1 text, v2 text) LANGUAGE plpgsql AS
$$                 
BEGIN   
    RETURN QUERY 
    WITH tbl AS 
    (
        SELECT 'a' AS aa, 'b' AS bb     
    )
    SELECT aa as v1, bb as v2 from tbl;
END;   
$$;

select * from f1();