Subject: Code examples for 39.6.1. Returning From a Function

From: Erwin Brandstetter <[email-masked]>
Date: Mon, 08 Apr 2013 00:08:16 +0200

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

---

From: Peter Eisentraut <[email-masked]>
Date: Sun, 21 Apr 2013 22:24:44 -0400

> |CREATE  FUNCTION  check_available_flightid(date)

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

> http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#AEN56293

Fixed.

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs