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-RETURNINGCREATE 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
