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

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

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.