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.

Mureinik · Accepted Answer · 2018-04-17 17:04:39Z

4

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;

edited Apr 17, 2018 at 17:04

answered Apr 17, 2018 at 15:58

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Common Expression Table (CTE) in Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related