Are these two PostgreSQL functions equivalent? (RETURNS TABLE and RETURNS SETOF)

I am dealing with two PostgreSQL installs at the same time: My local environment and the real remote server. Sadly the server has an old version (8.3.11) and my local environment is newer (9.4).

I don't have means to update the remote server at the moment, so I am converting a function that runs well in 9.4 (it uses RETURNS TABLE) to a function that should be fine in 8.3.11 (it should use RETURNS SETOF).

But while the local environment function works well and it gives good results, the remote one always yields no result (using the very same tables!)

So, are these two fully equivalent?

Newer function for local environment:

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
  RETURNS TABLE (game_date date, is_home varchar, is_away varchar) AS $$
BEGIN
   RETURN QUERY  
   SELECT g.game_date, p1.team_name AS plays_at_home, p2.team_name AS plays_away
   FROM pra2.game g
   JOIN pra2.team p1 ON g.is_home = p1.team_id
   JOIN pra2.team p2 ON g.is_away = p2.team_id
   WHERE g.game_date = $1;
   IF NOT FOUND THEN
      RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
   END IF;
   RETURN;
END
$$
   LANGUAGE plpgsql;

And here the function I have modified to use SETOF

CREATE TYPE return_type AS 
(game_date date,
is_home varchar,
is_away varchar);

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
  RETURNS SETOF return_type AS $$ 
DECLARE
   _rec return_type;
BEGIN
   RETURN QUERY  
   SELECT g.game_date, p1.team_name AS plays_at_home, p2.team_name AS plays_away
   FROM pra2.game g
   JOIN pra2.team p1 ON g.is_home = p1.team_id
   JOIN pra2.team p2 ON g.is_away = p2.team_id
   WHERE g.game_date = $1;
   IF NOT FOUND THEN
   RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
   END IF;
      RETURN next _rec;
END
$$
   LANGUAGE plpgsql;

It gives no error message at all, it runs ok, but it just yields no results (it always raises the exception message), so I am wondering whether there is something wrongly set up in the SETOF query ...

Solution

Judging from the documentation, RETURN QUERY doesn't set FOUND in PostgreSQL 8.3. (The relevant documentation for PostgreSQL 9.1 is at http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS; the corresponding statement does not appear in the corresponding PostgreSQL 8.3 documentation at http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS.) So your IF NOT FOUND check isn't doing what you want.

To be honest, I'm not sure what the best way to accomplish this is in PostgreSQL 8.3. One option is to write something like this:

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
RETURNS SETOF return_type AS $$ 
DECLARE _rec return_type;
        has_rec boolean;
BEGIN
    has_rec := false;
    FOR _rec IN
        SELECT g.game_date, p1.team_name AS plays_at_home, p2.team_name AS plays_away
        FROM pra2.game g
        JOIN pra2.team p1 ON g.is_home = p1.team_id
        JOIN pra2.team p2 ON g.is_away = p2.team_id
        WHERE g.game_date = $1
    LOOP
        has_rec := true;
        RETURN NEXT _rec;
    END LOOP;
    IF NOT has_rec THEN
        RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
    END IF;
END
$$ LANGUAGE plpgsql;

(Disclaimer: not tested.)