Re: FUNCTION problem

----- "Peter Willis" <peterw(at)borstad(dot)com> wrote:

> Adrian Klaver wrote:
> > 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-STRUCTURES-LOOPS
> >
> >
>
>
> Thank you for the pointer.
>
> I tried using FOR/RETURN NEXT as suggested but now get a
> different error:
>
>
> CREATE OR REPLACE FUNCTION test_function(integer)
> RETURNS SETOF record AS
> $BODY$
> DECLARE croid integer;
> DECLARE R RECORD;
> BEGIN
> SELECT INTO croid 2;
>
> FOR R IN SELECT croid,$1 LOOP
> RETURN NEXT R;
> END LOOP;
> RETURN;
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
>
> There is now an error :
>
> ERROR: set-valued function called in context that cannot accept a
> set
> CONTEXT: PL/pgSQL function "test_function" line 7 at RETURN NEXT
>
> ********** Error **********
>
> ERROR: set-valued function called in context that cannot accept a set
> SQL state: 0A000
> Context: PL/pgSQL function "test_function" line 7 at RETURN NEXT
>
>
>
> PostgreSQL doesn't seem to see 'R' as being a
> SET OF RECORD....
>
>
> Peter

Did you happen to catch this:
Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause

Try:
select * from test_function(1)

Adrian Klaver
aklaver(at)comcast(dot)net