public inbox for [email protected]  

help / color / mirror / Atom feed

Code examples for 39.6.1. Returning From a Function
2+ messages / 2 participants
[nested] [flat]

* Code examples for 39.6.1. Returning From a Function
@ 2013-04-07 22:08  Erwin Brandstetter <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Erwin Brandstetter @ 2013-04-07 22:08 UTC (permalink / raw)
  To: pgsql-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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Code examples for 39.6.1. Returning From a Function
@ 2013-04-22 02:24  Peter Eisentraut <[email protected]>
  parent: Erwin Brandstetter <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Eisentraut @ 2013-04-22 02:24 UTC (permalink / raw)
  To: Erwin Brandstetter <[email protected]>; +Cc: pgsql-docs

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




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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2013-04-22 02:24 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-07 22:08 Code examples for 39.6.1. Returning From a Function Erwin Brandstetter <[email protected]>
2013-04-22 02:24 ` Peter Eisentraut <[email protected]>

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