Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOxkn-0001Sd-Ub for pgsql-docs@arkaria.postgresql.org; Sun, 07 Apr 2013 22:08:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UOxkn-00079P-FV for pgsql-docs@arkaria.postgresql.org; Sun, 07 Apr 2013 22:08:01 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOxkm-00079K-Ko for pgsql-docs@postgresql.org; Sun, 07 Apr 2013 22:08:00 +0000 Received: from mail-ee0-f49.google.com ([74.125.83.49]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UOxke-0001wD-OV for pgsql-docs@postgresql.org; Sun, 07 Apr 2013 22:07:59 +0000 Received: by mail-ee0-f49.google.com with SMTP id l10so826486eei.22 for ; Sun, 07 Apr 2013 15:07:51 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=x-received:message-id:date:from:user-agent:mime-version:to:subject :content-type:x-gm-message-state; bh=xX1yY5skAcJMdKC25JqJ2f4RY9whHQV6ehhAyiYSeUA=; b=NbGasEEoESU7PV5YomW4QlLRP8HESln+pLBMtTUPKF55uLdo7BV4Wyv+VeGyXS2mg3 tq+O2z7bWc9fg2fdrqajRj2558M0E7JQAS1O3ZcX0rrYyrEeyPa4DgjZB3epN3K1TeWy sbYq7GrOUlTwHvxxjEBAi13tRamiZjMWPxp3z1Guac/EPFZAx0F+vJs9L5MIZn/nddcS II84Jh1O8Se+zZk+RMkdFCypDsrZ9jc/hAQNR3OZNcK2ZAALCHk50ThfGMIBTLQoYjoy csEaVxlDUmuwJqo+Cjc7aasAfPVbb+Xbe3bPKuDm+BdpaRAmK0NrT04ZsmegUHw+WIbT YXnA== X-Received: by 10.14.182.72 with SMTP id n48mr42893202eem.3.1365372471304; Sun, 07 Apr 2013 15:07:51 -0700 (PDT) Received: from [192.168.2.100] (chello080109013165.10.14.univie.teleweb.at. [80.109.13.165]) by mx.google.com with ESMTPS id s47sm28568311eeg.8.2013.04.07.15.07.49 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sun, 07 Apr 2013 15:07:50 -0700 (PDT) Message-ID: <5161EE50.6090004@falter.at> Date: Mon, 08 Apr 2013 00:08:16 +0200 From: Erwin Brandstetter User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130328 Thunderbird/17.0.5 MIME-Version: 1.0 To: pgsql-docs@postgresql.org Subject: Code examples for 39.6.1. Returning From a Function Content-Type: multipart/alternative; boundary="------------080600070502080902080101" X-Gm-Message-State: ALoCoQmzf+FJjEq8p/UPuIoqd7gKwakHWsFPmgCin98p18IwCrfBJ8eQ6ye1ol11nvASMH42PJH4 X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org This is a multi-part message in MIME format. --------------080600070502080902080101 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------080600070502080902080101 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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


--------------080600070502080902080101--