0

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
user1409708
  • 953
  • 2
  • 11
  • 20

1 Answers1

1

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();
v1 v2
a b
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • The function is also simple enough to be `language sql` instead of `language plpgsql`, which would let it lose the `begin`, `return query` and `end;` altogether. [Here](https://stackoverflow.com/a/24771561/5298879)'s on the benefits of selecting one function `language` over the other. I +1'd already - even in the current form, it already solves the problem and provides useful additional info. – Zegarek Dec 05 '21 at 15:20