pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: control reached end of function without RETURN
6+ messages / 4 participants
[nested] [flat]
* ERROR: control reached end of function without RETURN
@ 2005-02-11 18:51 Ignacio Colmenero <ignacio.colmenero@micotan.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ignacio Colmenero @ 2005-02-11 18:51 UTC (permalink / raw)
To: pgsql-general
Hi all.
I created a function, using EMS:
CREATE OR REPLACE FUNCTION "geo_schema"."search_geo" () RETURNS SETOF
varchar AS
$body$
declare x_longlat cursor for select long_lat from h2s ;
declare var_longlat public.geometry ;
declare x_id varchar ;
begin
open x_longlat ;
<<loop1>>
loop
fetch x_longlat into var_longlat ;
if not found then
close x_longlat ;
exit ;
end if ;
select field_id from geo_table
where within(var_longlat, geo_polygon) limit 1 into x_id ;
if x_id <> '' then
return next x_id ;
end if ;
end loop ;
end ;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
When I debug it (using EMS tools), it works fine, and I get 73 rows, but
when I run it like:
select * from search_geo() ;
I get the error: ERROR: control reached end of function without RETURN
What can be wrong?
TIA
Ignacio.
-----------------------------------------------
Ignacio Colmenero
Software Development
Micotan Software Company Ltd.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: control reached end of function without RETURN
@ 2005-02-11 19:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ignacio Colmenero <ignacio.colmenero@micotan.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-02-11 19:28 UTC (permalink / raw)
To: Ignacio Colmenero <ignacio.colmenero@micotan.com>; +Cc: pgsql-general
"Ignacio Colmenero" <ignacio.colmenero@micotan.com> writes:
> I get the error: ERROR: control reached end of function without RETURN
> What can be wrong?
You don't have a RETURN statement.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: control reached end of function without RETURN
@ 2005-02-11 19:49 Ignacio Colmenero <ignacio.colmenero@micotan.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Ignacio Colmenero @ 2005-02-11 19:49 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Thanks Tom.
I didn't know I needed a RETURN statement at the end. I assumed that the
"return next" statement would be enough. It works fine now.
-----------------------------------------------
Ignacio Colmenero
Software Development
Micotan Software Company Ltd.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: February 11, 2005 12:28 PM
To: Ignacio Colmenero
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ERROR: control reached end of function without RETURN
"Ignacio Colmenero" <ignacio.colmenero@micotan.com> writes:
> I get the error: ERROR: control reached end of function without RETURN
> What can be wrong?
You don't have a RETURN statement.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 6+ messages in thread
* ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.
@ 2021-09-13 11:23 Herwig Goemans <herwig.goemans@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Herwig Goemans @ 2021-09-13 11:23 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi Support,
I get an error in a stored procedure - not a stored function mind you -
control reached end of function without RETURN
The error occurs when doing an exit <label> in a stored procedure. Now
I cannot give all of the stored procedures involved but I can simulate
te problem with a very simple block:
DO
$BODY$
<<simple_block>>
BEGIN
begin
exit simple_block;
-- for demo purposes
raise notice '%', 'unreachable!';
end;
raise notice '%', 'End of block';
end;
$BODY$
This will return the error on Postgres version 13, Version 12 and 9.
Now I thought this was text-book behaviour of exiting a code block. I do
not like programming this way but it is part of a migration project of
Oracle to PG.
What do I miss ?
Kind regards,
If you put this code snippet in a file and execute like:
postgres@herwig-NUC10i7FNH:~$ psql -f do_test.sql
psql:do_test.sql:12: ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function inline_code_block
Herwig
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.
@ 2021-09-13 11:44 Pavel Stehule <pavel.stehule@gmail.com>
parent: Herwig Goemans <herwig.goemans@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2021-09-13 11:44 UTC (permalink / raw)
To: Herwig Goemans <herwig.goemans@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Hi
po 13. 9. 2021 v 13:23 odesílatel Herwig Goemans <herwig.goemans@gmail.com>
napsal:
> Hi Support,
>
>
> I get an error in a stored procedure - not a stored function mind you -
> control reached end of function without RETURN
>
> The error occurs when doing an exit <label> in a stored procedure. Now
> I cannot give all of the stored procedures involved but I can simulate
> te problem with a very simple block:
>
>
>
> DO
> $BODY$
> <<simple_block>>
> BEGIN
> begin
> exit simple_block;
> -- for demo purposes
> raise notice '%', 'unreachable!';
> end;
> raise notice '%', 'End of block';
> end;
> $BODY$
>
> This will return the error on Postgres version 13, Version 12 and 9.
>
>
> Now I thought this was text-book behaviour of exiting a code block. I do
> not like programming this way but it is part of a migration project of
> Oracle to PG.
>
> What do I miss ?
>
This is Postgres's bug
You can use an just workaround - extra outer block BEGIN END;
Regards
Pavel
> Kind regards,
>
>
> If you put this code snippet in a file and execute like:
>
> postgres@herwig-NUC10i7FNH:~$ psql -f do_test.sql
> psql:do_test.sql:12: ERROR: control reached end of function without RETURN
> CONTEXT: PL/pgSQL function inline_code_block
>
> Herwig
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.
@ 2021-09-13 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2021-09-13 16:04 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Herwig Goemans <herwig.goemans@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
Pavel Stehule <pavel.stehule@gmail.com> writes:
> po 13. 9. 2021 v 13:23 odesílatel Herwig Goemans <herwig.goemans@gmail.com>
> napsal:
>> I get an error in a stored procedure - not a stored function mind you -
>> control reached end of function without RETURN
>> The error occurs when doing an exit <label> in a stored procedure.
> This is Postgres's bug
Yeah, agreed. Looks reasonably simple to fix, though.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-09-13 16:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-11 18:51 ERROR: control reached end of function without RETURN Ignacio Colmenero <ignacio.colmenero@micotan.com>
2005-02-11 19:28 ` Re: ERROR: control reached end of function without RETURN Tom Lane <tgl@sss.pgh.pa.us>
2005-02-11 19:49 ` Re: ERROR: control reached end of function without RETURN Ignacio Colmenero <ignacio.colmenero@micotan.com>
2021-09-13 11:23 ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block. Herwig Goemans <herwig.goemans@gmail.com>
2021-09-13 11:44 ` Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block. Pavel Stehule <pavel.stehule@gmail.com>
2021-09-13 16:04 ` Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block. Tom Lane <tgl@sss.pgh.pa.us>
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