Postgresql Common Expression Table (CTE) in Function

Question

I'm trying to use CTE in PostgreSQL function and returning the CTE as table. But I couldn't manage to compile the function as it says ERROR: syntax error at end of input in the select query. Could someone point me what I'm missing here.

CREATE OR REPLACE FUNCTION my_func(name varchar) RETURNS TABLE (hours integer) AS $$
BEGIN
WITH a AS ( 
    SELECT hours FROM name_table tbl where tbl.name= name; <- giving error here
        )       
        RETURN QUERY SELECT hours FROM a;
END;
$$ LANGUAGE plpgsql;

PS: I'm on PostgreSQL 9.6 if that helps.

Answer 1

The CTE expression is part of the query, so it needs to come immediately after the return query clause, not before it. Additionally, to avoid syntax errors later on, you should select a parameter name that ins't ambiguous with the names of the columns, and fully qualify the columns you're querying:

CREATE OR REPLACE FUNCTION my_func(v_name varchar)
RETURNS TABLE (hours integer) AS $$
BEGIN
RETURN QUERY WITH a AS (
    SELECT tbl.hours 
    FROM name_table tbl
    WHERE name = v_name
    )
    SELECT a.hours FROM a;
END;
$$ LANGUAGE plpgsql;