Any way to get a more meaningful error message in postgres?

Question

Let's have some tea ...

CREATE OR REPLACE FUNCTION allRelevantTeas() RETURNS TABLE(tea_id INTEGER) AS $function$
DECLARE
    result REFCURSOR;
    stmt TEXT;
    countries_with_tea TEXT[] := array['england', 'turkey', 'india', 'japan', 'china'];
BEGIN
    stmt := '';
    FOR tea_drinker in countries_with_tea LOOP
        stmt := stmt || format($$(SELECT tea_id FROM %I)$$, tea_drinker);
        IF tea_drinker <> 'china' THEN
            stmt := stmt || $$ UNION $$;
        END IF;
    END LOOP;

    OPEN result FOR EXECUTE stmt;
    RETURN result;
END $function$
LANGUAGE plpgsql STABLE;

SELECT * FROM allRelevantTeas();

Let's test this ...

syntax error at or near "countries_with_tea"

I don't see it. It's probably quite obvious, but I just don't see it.

Does anybody spot what's wrong with this? Or if not, is there any way I can get a more meaningful error from postgres than "there was a syntax error (but I don't tell you what it was)"?

Answer 1

Just a small off hand note. I often look at my code and ask "Is there anything that is subject to breaking at any foreseeable change and can I prevent it now?" It has served my well for many years, and I almost always can find something. There is one here. The statement "If tea_drinker ... end if" is very fragile. What happens when another country is added to the array and gets added after China. Answer, your SQL fails when executed. A hardened version would be to check the position of the current tea_drinker to the last enter in the array. This can be done with the array functions, array_position and array_length.

-- Instead of: 
IF tea_drinker <> 'china' THEN
   stmt := stmt || $$ UNION $$;
END IF;

-- Use 
if array_position(countries_with_tea, tea_drinker) <> array_length(countries_with_tea, 1)
then 
   stmt := stmt || $$ UNION $$;
end if;

Answer 2

Your loop over the is wrong, you need to use FOREACH to loop through the elements of an array:

CREATE OR REPLACE FUNCTION allRelevantTeas() RETURNS TABLE(tea_id INTEGER) AS $function$
DECLARE
    stmt TEXT;
    tea_drinker text;
    countries_with_tea TEXT[] := array['england', 'turkey', 'india', 'japan', 'china'];
BEGIN
    stmt := '';
    FOREACH tea_drinker in array countries_with_tea LOOP
        stmt := stmt || format($$(SELECT tea_id FROM %I)$$, tea_drinker);
        IF tea_drinker <> 'china' THEN
            stmt := stmt || $$ UNION $$;
        END IF;
    END LOOP;

    RETURN query execute stmt;
END $function$
LANGUAGE plpgsql STABLE;

You don't really need a UNION, you can use return query to return multiple results:

CREATE OR REPLACE FUNCTION allrelevantteas() 
  RETURNS TABLE(tea_id INTEGER) AS $function$
DECLARE
    stmt TEXT;
    tea_drinker text;
    countries_with_tea TEXT[] := array['england', 'turkey', 'india', 'japan', 'china'];
BEGIN
    stmt := '';
    FOREACH tea_drinker in array countries_with_tea LOOP
      stmt := format($$(SELECT tea_id FROM %I)$$, tea_drinker);
      return query execute stmt;
    END LOOP;
END $function$
LANGUAGE plpgsql STABLE;

Online example

---

If you need that a log, it might be better to use table inheritance or a view to create a single table that contains all others.

Answer 3

a_horse_with_no_name has told you how to fix the error, but for getting a more meaningful error message, I already get one, using psql:

ERROR:  syntax error at or near "countries_with_tea"
LINE 8:     FOR tea_drinker in countries_with_tea LOOP
                               ^

If your only see you the first line and not the next two, then you are using some tool which is not your friend.