Hi All,



I've been tossing up an idea here and was wondering what people
think...I'm basically trying to allow user defined functions (in a
limited form) without the hassle of having to go through the full C type
function create and link.



It seems to me that the simplest way to do this is to define a db
procedure with a specified return type...it can be any valid ingres data
type. The procedure can only raise an error or return something of that
type. The procedure has access to all the normal 4GL stuff a procedure
can access. So these won't be as powerful as full blown OME functions,
but will allow people to make function like procedures.



Because the procedure has a gaurunteed return type we could then call
the procedure from anywhere we can use a column name. We can use the
existing callproc or execute procedure syntax.



What I would envisage is something like...

create procedure my_proc(

p1 integer not null,

p2 integer not null,

p3 integer not null

) as

return integer not null

declare

msg varchar(256);

total integer not null;

begin

if (p3 = 0) then

msg = 'p3 may not be zero you goose!';

raise error 9999 msg;

endif;

total = (p1 +p2)*p3;

return total;

end;



select ...whatever... from ...

where callproc my_proc (p1=a, p2=b, p3=c) = 3;



or.. select a,b, callproc my_proc(p1=a, p2=b, p3=c);



What do people think of this?



Martin Bowes

Hi Marty,

Why not "create function" ?
Do we need the "callproc" keyword ?
Can we have positional parameters ?
I'd prefer to see the RETURN keyword before the AS


create function volume (
X integer default 0,
Y integer default 0,
Z integer default 0
) returns varchar(5) as
begin
declare HowBig varchar(5)

if X * Y * Z >=100
set HowBig = 'big'
elseif X * Y * Z = 0
set HowBig = 'tiny'
else
set HowBig = 'small'

return HowBig
end


select volume(X=height,Y=width,Z=depth), .... from room_dimensions..

or

select volume(height,width,depth), .... from room_dimensions..

Of course I would never use a function in a where clause because that leads
to table scans.

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com]On Behalf Of Martin
Bowes
Sent: Thursday, 8 January 2009 8:44 PM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Enhancement to DB procedures


Hi All,



I've been tossing up an idea here and was wondering what people think.I'm
basically trying to allow user defined functions (in a limited form) without
the hassle of having to go through the full C type function create and link.



It seems to me that the simplest way to do this is to define a db
procedure with a specified return type.it can be any valid ingres data type.
The procedure can only raise an error or return something of that type. The
procedure has access to all the normal 4GL stuff a procedure can access. So
these won't be as powerful as full blown OME functions, but will allow
people to make function like procedures.



Because the procedure has a gaurunteed return type we could then call the
procedure from anywhere we can use a column name. We can use the existing
callproc or execute procedure syntax.



What I would envisage is something like.

create procedure my_proc(

p1 integer not null,

p2 integer not null,

p3 integer not null

) as

return integer not null

declare

msg varchar(256);

total integer not null;

begin

if (p3 = 0) then

msg = 'p3 may not be zero you goose!';

raise error 9999 msg;

endif;

total = (p1 +p2)*p3;

return total;

end;



select .whatever. from .

where callproc my_proc (p1=a, p2=b, p3=c) = 3;



or.. select a,b, callproc my_proc(p1=a, p2=b, p3=c);



What do people think of this?



Martin Bowes

Hi Paul,



Why not "create function" ?

I don't have a problem with that. And in fact it might be better to make
the distinction as unlike a directly executed procedure we may need to
stop commands like commit and rollback.



I'd like to allow select, insert, update, delete statements as well.



Do we need the "callproc" keyword ?

It may be handy, so that Ingres would know its not a built-in function
or an OME function.



Can we have positional parameters ?

I'd like to keep it as close as possible to the existing procedure call
syntax as possible. But hey, it's a wish list at the moment.



I'd prefer to see the RETURN keyword before the AS

Yeah that looks nicer, and is more equivalent to the current row
producing procedure syntax.



Marty

I'm pretty sure this is already in the works. Not sure what version it's
planned for though.

--
Paul Mason

Hi Paul,



I'd really like to find out if this was the case. Details on what was
intended and when it was expected for delivery would be great.



Marty



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Paul
Mason
Sent: 08 January 2009 12:30
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Enhancement to DB procedures



I'm pretty sure this is already in the works. Not sure what version it's
planned for though.

--
Paul Mason