No Return??

On 13/04/2008 21:07, Bob Pawley wrote:

When I try it as a whole I get the message "control reached end of
trigger procedure without RETURN."

Hi Bob,

If the "IF" branch of the outer IF is executed, then the flow of
execution won't hit a RETURN anywhere; I reckon this is what's causing
the error.

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Bob,

if pumpnumber not in (1,2) that function does not return anything.

End If;

----> at this end if it ends
so you have to return sth. here

END;

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

On Sunday 13 April 2008 1:07 pm, Bob Pawley wrote:

I'm getting a little frustrated with this problem.

Can anyone tell me what is wrong with the following code.

I have tested the portions separately and they all work.

When I try it as a whole I get the message "control reached end of trigger
procedure without RETURN."

Any help greatly appreciated.

Bob

Declare
pumpnumber integer;

Begin

Select count(*) Into pumpnumber From p_id.devices, p_id.processes
Where device_number = '11'
and p_id.devices.fluid_id = p_id.processes.fluid_id
and p_id.processes.ip_op_equipment = 'op';

If pumpnumber = 1 then
Update p_id.devices
Set number = '#1'
From p_id.processes
Where p_id.devices.number is null
and p_id.devices.device_number = '11'
and p_id.devices.fluid_id = p_id.processes.fluid_id
and p_id.processes.ip_op_equipment = 'op' ;

Else If pumpnumber = 2 Then

Should be elsif or elseif

Update p_id.devices
Set number = '#2'
From p_id.processes
Where p_id.devices.number is null
and p_id.devices.device_number = '11'
and p_id.devices.fluid_id = p_id.processes.fluid_id
and p_id.processes.ip_op_equipment = 'op' ;

End If;
RETURN NULL;
End If;

Eliminate this End if .

END;

I have tried 'Return New' and 'Return Result' without luck, and if I leave
off either of the two 'End If ' statements the procedure returns an error.

B

--
Adrian Klaver
aklaver@comcast.net

On Sun, Apr 13, 2008 at 01:07:26PM -0700, Bob Pawley wrote:

When I try it as a whole I get the message "control reached end of trigger
procedure without RETURN."

I've re-indented the code to make it a bit more obvious what's going on
in your old version:

DECLARE
pumpnumber integer;
BEGIN
SELECT count(*) INTO pumpnumber [...]
IF pumpnumber = 1 THEN
UPDATE p_id.devices [...]
ELSE
IF pumpnumber = 2 THEN
UPDATE p_id.devices [...]
END IF;
RETURN NULL;
END IF;
END;

I have tried 'Return New' and 'Return Result' without luck, and if I leave
off either of the two 'End If ' statements the procedure returns an error.

I think you're looking for either "ELSIF" or "ELSEIF", you've got
white space between the ELSE and the IF which is introducing a new
sub-expression. Either that, or move the RETURN after the final END IF.

Sam

--- Bob Pawley <rjpawley@shaw.ca> wrote:

I'm getting a little frustrated with this problem.

Can anyone tell me what is wrong with the following
code.

I have tested the portions separately and they all
work.

When I try it as a whole I get the message "control
reached end of trigger
procedure without RETURN."

Any help greatly appreciated.

Bob

Declare
pumpnumber integer;

Begin

Select count(*) Into pumpnumber From p_id.devices,
p_id.processes
Where device_number = '11'
and p_id.devices.fluid_id = p_id.processes.fluid_id
and p_id.processes.ip_op_equipment = 'op';

If pumpnumber = 1 then
Update p_id.devices
Set number = '#1'
From p_id.processes
Where p_id.devices.number is null
and p_id.devices.device_number = '11'
and p_id.devices.fluid_id = p_id.processes.fluid_id
and p_id.processes.ip_op_equipment = 'op' ;

Else If pumpnumber = 2 Then
Update p_id.devices
Set number = '#2'
From p_id.processes
Where p_id.devices.number is null
and p_id.devices.device_number = '11'
and p_id.devices.fluid_id = p_id.processes.fluid_id
and p_id.processes.ip_op_equipment = 'op' ;

End If;
RETURN NULL;
End If;
END;

I have tried 'Return New' and 'Return Result'
without luck, and if I leave
off either of the two 'End If ' statements the
procedure returns an error.

Look at your flow control! Your return is within a
conditional block. If the condition for your first
returns false, flow goes to the very end of the
function and reaches "end" without encountering a
return statement.

Cheers,

Ted