Error: Returned type unknown at ordinal position 2, but query expects text-CodePudding

I created an SQL function in PostgreSQL that returns a table with text values, it is throwing error

NOTICE:  function func() does not exist, skipping

ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type unknown at ordinal position 2, but query expects text.
SQL state: 42804

Function definition:

CREATE OR REPLACE function func(a)
        RETURNS TABLE(num int, display text) AS $$
            SELECT CASE
                WHEN (num >= 10) THEN (10, 'MEDIUM')
                WHEN (num >= 20) THEN (20, 'HIGH')
                ELSE (0, 'LOW')
            END
        $$
        language sql stable strict;

CodePudding user response：

You could try casting the string literals explicitly to text using the double colon operator:

CREATE OR REPLACE function func(a)
    RETURNS TABLE(num int, display text) AS $$
        SELECT CASE
            WHEN (num >= 10) THEN (10, 'MEDIUM'::text)
            WHEN (num >= 20) THEN (20, 'HIGH'::text)
            ELSE (0, 'LOW'::text)
        END
    $$

language sql stable strict;