PostgreSQL stored procedure error

Question

I have a stored procedure:

CREATE OR REPLACE FUNCTION postalcode_lookup(ccode character(6))
RETURNS SETOF postalcode AS $BODY$
BEGIN
    WITH RECURSIVE tblParent AS 
    (
        SELECT * 
        FROM postalcode 
        WHERE postalcode.code = ccode

        UNION ALL 

        SELECT postalcode.* 
        FROM postalcode  
        JOIN tblParent ON postalcode.code = tblParent.parent
    )
    SELECT * FROM tblParent;

END;$BODY$ LANGUAGE plpgsql;

Compile successfully but when running I have

error: query has no destination for result data;SQL state: 42601;

Hint:> If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function postalcode_lookup(character) line 3 at SQL statement

Please help me fix. Many thanks.

Answer 1

In case of Function we have to return values

if you we write function as follow we can get desired results

    CREATE OR REPLACE FUNCTION postalcode_lookup(ccode character(6))
    RETURNS refcursor AS $BODY$
    DECLARE 
    cur_postalcode refcursor;
    BEGIN
Open cur_postalcode for
        WITH RECURSIVE tblParent AS 
        (
            SELECT * 
            FROM postalcode 
            WHERE postalcode.code = ccode

            UNION ALL 

            SELECT postalcode.* 
            FROM postalcode  
            JOIN tblParent ON postalcode.code = tblParent.parent
        )
        SELECT * FROM tblParent;
    return cur_postalcode;

    END;$BODY$ LANGUAGE plpgsql;

on executing this function, you will get a cursor. to get values from cursor run given query.

SELECT postalcode_lookup(--ccode);

fetch all "<unnamed portal 1>";

Hope it helps.

Answer 2

As you don't have any procedural code, use a plain SQL function:

CREATE OR REPLACE FUNCTION postalcode_lookup(ccode character(6))
   RETURNS SETOF postalcode AS 
$BODY$
    WITH RECURSIVE tblParent AS 
    (
        SELECT * 
        FROM postalcode 
        WHERE postalcode.code = ccode

        UNION ALL 

        SELECT postalcode.* 
        FROM postalcode  
        JOIN tblParent ON postalcode.code = tblParent.parent
    )
    SELECT * 
    FROM tblParent;
$BODY$ LANGUAGE sql;

For completeness, the PL/pgSQL version of this would need to use RETURN QUERY:

CREATE OR REPLACE FUNCTION postalcode_lookup(ccode character(6))
   RETURNS SETOF postalcode AS 
$BODY$
BEGIN
    RETURN QUERY --<< this does the magic
    WITH RECURSIVE tblParent AS 
    (
        SELECT * 
        FROM postalcode 
        WHERE postalcode.code = ccode

        UNION ALL 

        SELECT postalcode.* 
        FROM postalcode  
        JOIN tblParent ON postalcode.code = tblParent.parent
    )
    SELECT * 
    FROM tblParent;
END;
$BODY$ LANGUAGE plpgsql;

But the plain SQL function should be preferred as it will be faster (and can be optimized better when using in a more complex statement, e.g. with a join or with additional where conditions.

In both cases the function can be use like a table:

select * 
from postalcode_lookup('ABCDEF');

---

Unrelated, but: using the character data type is almost always a bad choice. Use varchar or text instead.