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 integerAS
$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