Code examples for 39.6.1. Returning From a Function

Aloha!

Repost, since the first attempt on 03.04.2013 14:31 was before I subsrcibed to pgsql-docs and doesn't seem to have arrived.

As advised by Pavel here:
http://stackoverflow.com/questions/15731247/postgresql-function-syntax-error/15731425#15731425
I am sending this as possible code example for:
http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

CREATE FUNCTION check_available_flightid(date) RETURNS SETOF integer AS 
$func$
BEGIN   RETURN QUERY  SELECT flightid  FROM   flight  WHERE  flightdate >= $1  AND    flightdate < ($1 + 1);
  -- Since execution is not finished, we can check whether rows were returned  IF NOT FOUND     RAISE EXCEPTION 'No flight at %.', $1;  END IF;
  RETURN;   
END
$func$ LANGUAGE plpgsql

While being at it, the example at
http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#AEN56293
could be improved.
Instead of:

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE   r foo%rowtype;
BEGIN   FOR r IN SELECT * FROM foo   WHERE fooid > 0   LOOP       -- can do some processing here       RETURN NEXT r; -- return current row of SELECT   END LOOP;   RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

Use:

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE   r foo%rowtype;
BEGIN   FOR r IN       SELECT * FROM foo WHERE fooid > 0   LOOP       -- can do some processing here       RETURN NEXT r; -- return current row of SELECT   END LOOP;   RETURN;
END
$BODY$
LANGUAGE plpgsql;

- Most importantly plpgsql is an identifier and should not be quoted!
- Using unquoted, mixed-case identifers (getAllFoo) is a bad example.
- Minor formatting.


Regards
Erwin