Обсуждение: FUNCTION problem
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
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 -- Adrian Klaver aklaver@comcast.net
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; BEGINSELECT 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
----- "Peter Willis" <peterw@borstad.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@comcast.net
Adrian Klaver wrote: > > > 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) > I did miss that, but using that method to query the function didn't work either. Postgres doesn't see the result as a tabular set of records. Even if I replace the FOR loop with: <quote> FOR R IN SELECT * FROM pg_database LOOPRETURN NEXT R; END LOOP; </quote> I get the same error(s). I don't think postgres likes the unrelated 'SELECT INTO <variable> [column] FROM [QUERY] LIMIT 1' lines before the FOR loop... I think I need to go back and approach the function from a different direction. Thanks for all the pointers. Peter
On Thursday 02 April 2009 4:22:06 pm Peter Willis wrote: > Adrian Klaver wrote: > > 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) > > I did miss that, but using that method to query the function > didn't work either. Postgres doesn't see the result as a > tabular set of records. > > Even if I replace the FOR loop with: > > <quote> > FOR R IN SELECT * FROM pg_database LOOP > RETURN NEXT R; > END LOOP; > > </quote> > > I get the same error(s). I don't think postgres likes > the unrelated 'SELECT INTO <variable> [column] FROM [QUERY] LIMIT 1' > lines before the FOR loop... > > I think I need to go back and approach the function from a > different direction. > > Thanks for all the pointers. > > Peter Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver aklaver@comcast.net
On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > > Now I remember. Its something that trips me up, the RECORD in RETURN setof > RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for > a better explanation- > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PL >PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a > placeholder. One should also realize that when a PL/pgSQL function is > declared to return type record, this is not quite the same concept as a > record variable, even though such a function might use a record variable to > hold its result. In both cases the actual row structure is unknown when the > function is written, but for a function returning record the actual > structure is determined when the calling query is parsed, whereas a record > variable can change its row structure on-the-fly. > > > > -- > Adrian Klaver > aklaver@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGINSELECT INTO croid 2;SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver aklaver@comcast.net
On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > Now I remember. Its something that trips me up, the RECORD in RETURN > > setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See > > below for a better explanation- > > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# > >PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, > > only a placeholder. One should also realize that when a PL/pgSQL function > > is declared to return type record, this is not quite the same concept as > > a record variable, even though such a function might use a record > > variable to hold its result. In both cases the actual row structure is > > unknown when the function is written, but for a function returning record > > the actual structure is determined when the calling query is parsed, > > whereas a record variable can change its row structure on-the-fly. > > > > > > > > -- > > Adrian Klaver > > aklaver@comcast.net > > For this particular case the following works. > > CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record > AS $Body$ > DECLARE croid integer; > DECLARE R RECORD; > BEGIN > SELECT INTO croid 2; > SELECT INTO R croid,$1; > RETURN R; > END; > > $Body$ > LANGUAGE plpgsql; > > -- > Adrian Klaver > aklaver@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int);c1 | c2 ----+---- 2 | 1 (1 row) -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote: > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: >>> Now I remember. Its something that trips me up, the RECORD in RETURN >>> setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See >>> below for a better explanation- >>> http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# >>> PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, >>> only a placeholder. One should also realize that when a PL/pgSQL function >>> is declared to return type record, this is not quite the same concept as >>> a record variable, even though such a function might use a record >>> variable to hold its result. In both cases the actual row structure is >>> unknown when the function is written, but for a function returning record >>> the actual structure is determined when the calling query is parsed, >>> whereas a record variable can change its row structure on-the-fly. >>> >>> >>> >>> -- >>> Adrian Klaver >>> aklaver@comcast.net >> For this particular case the following works. >> >> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record >> AS $Body$ >> DECLARE croid integer; >> DECLARE R RECORD; >> BEGIN >> SELECT INTO croid 2; >> SELECT INTO R croid,$1; >> RETURN R; >> END; >> >> $Body$ >> LANGUAGE plpgsql; >> >> -- >> Adrian Klaver >> aklaver@comcast.net > > Forgot to show how to call it. > > test=# SELECT * from test_function(1) as test(c1 int,c2 int); > c1 | c2 > ----+---- > 2 | 1 > (1 row) > > Ah!, I see what you mean about the definition of 'RECORD'. (The lights come on...) And here I thought it would all be so simple..... You show a valid, and most informative solution. This should get things working for me. Thank you very much for your help. Peter
----- "Peter Willis" <peterw@borstad.com> wrote: > Adrian Klaver wrote: > > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > >>> Now I remember. Its something that trips me up, the RECORD in > RETURN > >>> setof RECORD is not the same thing as the RECORD in DECLARE > RECORD. See > >>> below for a better explanation- > >>> > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# > >>> PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data > type, > >>> only a placeholder. One should also realize that when a PL/pgSQL > function > >>> is declared to return type record, this is not quite the same > concept as > >>> a record variable, even though such a function might use a record > >>> variable to hold its result. In both cases the actual row > structure is > >>> unknown when the function is written, but for a function returning > record > >>> the actual structure is determined when the calling query is > parsed, > >>> whereas a record variable can change its row structure > on-the-fly. > >>> > >>> > >>> > >>> -- > >>> Adrian Klaver > >>> aklaver@comcast.net > >> For this particular case the following works. > >> > >> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record > >> AS $Body$ > >> DECLARE croid integer; > >> DECLARE R RECORD; > >> BEGIN > >> SELECT INTO croid 2; > >> SELECT INTO R croid,$1; > >> RETURN R; > >> END; > >> > >> $Body$ > >> LANGUAGE plpgsql; > >> > >> -- > >> Adrian Klaver > >> aklaver@comcast.net > > > > Forgot to show how to call it. > > > > test=# SELECT * from test_function(1) as test(c1 int,c2 int); > > c1 | c2 > > ----+---- > > 2 | 1 > > (1 row) > > > > > > Ah!, I see what you mean about the definition of 'RECORD'. > (The lights come on...) > > And here I thought it would all be so simple..... > > You show a valid, and most informative solution. > This should get things working for me. If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument listto eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what yourinputs are, how you want to process them and how you want to return the output. > > Thank you very much for your help. > > Peter Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote: > > > If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argumentlist to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determinewhat your inputs are, how you want to process them and how you want to return the output. > '8.1+'?? Hmmm, I'm using 8.3. I could use that. I got the more complex version of the query to work by backing away from 'plpgsql' as the language and using 'sql' instead. I then nested (terribly ugly) my select statements to generate a single SQL query from all. This allows me to change the output of the query without needing to define a new set of output 'OUT' parameters each time I change things. I have use of the 'OUT' parameters with another set of functions though. Thanks for that. Peter