From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
To: peterw@borstad.com
Subject: Re: FUNCTION problem
Date: Wed, 1 Apr 2009 16:55:58 -0700
Message-ID: <200904011655.58801.aklaver@comcast.net> (raw)
In-Reply-To: <49D3F948.4060908@borstad.com>
References: <49D3F948.4060908@borstad.com>
On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote:
> Hello,
>
> I am having a problem with a FUNCTION.
> The function creates just fine with no errors.
>
> However, when I call the function postgres produces an error.
>
> Perhaps someone can enlighten me.
>
>
> --I can reproduce the error by making a test function
> --that is much easier to follow that the original:
>
> CREATE OR REPLACE FUNCTION test_function(integer)
> RETURNS SETOF RECORD AS
> $BODY$
> DECLARE croid integer;
> BEGIN
>
> --PERFORM A SMALL CALCULATION
> --DOESNT SEEM TO MATTER WHAT IT IS
>
> SELECT INTO croid 2;
>
> --A SELECT STATEMENT OUTPUTS RECORDS (one in this case)
> SELECT croid,$1;
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
>
>
>
> --The call looks like the following:
>
> SELECT test_function(1);
>
>
>
>
>
> --The resulting error reads as follows:
>
> ERROR: query has no destination for result data
> HINT: If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT: PL/pgSQL function "test_function" line 5 at SQL statement
>
> ********** Error **********
>
> ERROR: query has no destination for result data
> SQL state: 42601
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
> Context: PL/pgSQL function "test_function" line 5 at SQL statement
You have declared function to RETURN SETOF. In order for that to work you need
to do RETURN NEXT. See below for difference between RETURN and RETURN NEXT:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUC...
--
Adrian Klaver
aklaver@comcast.net
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: pgsql-sql@postgresql.org
Cc: aklaver@comcast.net, peterw@borstad.com
Subject: Re: FUNCTION problem
In-Reply-To: <200904011655.58801.aklaver@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox