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.

B


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
------------------------------------------------------------------

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--- 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general