public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erwin Brandstetter <[email protected]>
To: [email protected]
Subject: Code examples for 39.6.1. Returning From a Function
Date: Mon, 08 Apr 2013 00:08:16 +0200
Message-ID: <[email protected]> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-docs>

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-STATEMENT...

|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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Code examples for 39.6.1. Returning From a Function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox