Thread: 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

On Mon, 2013-04-08 at 00:08 +0200, Erwin Brandstetter wrote:
> 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

Could you send a patch for that?  I'm not exactly sure where it should
go.

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

Fixed.