Can table procedures (or row producing procedures) use the execute procedure statement?
Unless I'm doing something wrong (which is odds on) it appears that the procedure can be defined with an execute procedure call but the attempt to execute it will fail with:
E_QE0310 Unsupported procedure statement type: CALL
drop procedure trial_date_cast;
\p\g
create procedure trial_date_cast(
a_string varchar(40) not null not default
)
as declare
int_date ingresdate not null not default;
begin
int_date = date(:a_string);
return iierrornumber;
end;
\p\g
drop procedure is_date;
\p\g
create procedure is_date(
a_string varchar(40) not null not default
)
result row myrow (is_date integer not null)
as declare
int_string varchar(40) not null not default;
a_date integer not null not default;
begin
for select :a_string into :int_string
do
execute procedure trial_date_cast(a_string = :a_string) into :a_date;
return row (:a_date);
endfor;
end;
\p\g
declare global temporary table x_date(
a_string varchar(40) not null not default
) on commit preserve rows with norecovery;
insert into x_date(a_string) values('not a date');
insert into x_date(a_string) values('1/1/2011 11:13:45');
insert into x_date(a_string) values('1/11/2010');
insert into x_date(a_string) values('31/1/2002');
\p\g
select x.a_string,
select x.a_string, y.is_date
from x_date x, is_date(a_string = x.a_string) y
\p\g
drop procedure trial_date_cast;
\p\g
create procedure trial_date_cast(
a_string varchar(40) not null not default
)
as declare
int_date ingresdate not null not default;
begin
int_date = date(:a_string);
return iierrornumber;
end;
\p\g
drop procedure is_date;
\p\g
create procedure is_date(
a_string varchar(40) not null not default
)
result row myrow (is_date integer not null)
as declare
int_string varchar(40) not null not default;
a_date integer not null not default;
begin
for select :a_string into :int_string
do
execute procedure trial_date_cast(:int_string) into :a_date;
return row (:a_date);
endfor;
end;
\p\g
declare global temporary table x_date(
a_string varchar(40) not null not default
) on commit preserve rows with norecovery;
insert into x_date(a_string) values('not a date');
insert into x_date(a_string) values('1/1/2011 11:13:45');
insert into x_date(a_string) values('1/11/2010');
insert into x_date(a_string) values('31/1/2002');
\p\g
select x.a_string, y.is_date
from x_date x, is_date(a_string = x.a_string) y
\p\g
Can table procedures (or row producing procedures) use the execute procedure statement?
Unless I'm doing something wrong (which is odds on) it appears that the procedure can be defined with an execute procedure call but the attempt to execute it will fail with:
E_QE0310 Unsupported procedure statement type: CALL
drop procedure trial_date_cast;
\p\g
create procedure trial_date_cast(
a_string varchar(40) not null not default
)
as declare
int_date ingresdate not null not default;
begin
int_date = date(:a_string);
return iierrornumber;
end;
\p\g
drop procedure is_date;
\p\g
create procedure is_date(
a_string varchar(40) not null not default
)
result row myrow (is_date integer not null)
as declare
int_string varchar(40) not null not default;
a_date integer not null not default;
begin
for select :a_string into :int_string
do
execute procedure trial_date_cast(a_string = :a_string) into :a_date;
return row (:a_date);
endfor;
end;
\p\g
declare global temporary table x_date(
a_string varchar(40) not null not default
) on commit preserve rows with norecovery;
insert into x_date(a_string) values('not a date');
insert into x_date(a_string) values('1/1/2011 11:13:45');
insert into x_date(a_string) values('1/11/2010');
insert into x_date(a_string) values('31/1/2002');
\p\g
select x.a_string,
select x.a_string, y.is_date
from x_date x, is_date(a_string = x.a_string) y
\p\g
Aha, just found it in the documentation. Row producing procedures (and hence Table Procedures) cannot call a procedure.
Marty
From: Martin Bowes [mailto:martin.bo...@ctsu.ox.ac.uk]
Sent: 20 January 2012 09:39
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Table procedures and execute procedure
Whoops, sent some partially edited code...
It should have been:
drop procedure trial_date_cast;
\p\g
create procedure trial_date_cast(
a_string varchar(40) not null not default
)
as declare
int_date ingresdate not null not default;
begin
int_date = date(:a_string);
return iierrornumber;
end;
\p\g
drop procedure is_date;
\p\g
create procedure is_date(
a_string varchar(40) not null not default
)
result row myrow (is_date integer not null)
as declare
int_string varchar(40) not null not default;
a_date integer not null not default;
begin
for select :a_string into :int_string
do
execute procedure trial_date_cast(:int_string) into :a_date;
return row (:a_date);
endfor;
end;
\p\g
declare global temporary table x_date(
a_string varchar(40) not null not default
) on commit preserve rows with norecovery;
insert into x_date(a_string) values('not a date');
insert into x_date(a_string) values('1/1/2011 11:13:45');
insert into x_date(a_string) values('1/11/2010');
insert into x_date(a_string) values('31/1/2002');
\p\g
select x.a_string, y.is_date
from x_date x, is_date(a_string = x.a_string) y
\p\g
Roy Hann <specia...@processed.almost.meat> wrote:
> > Aha, just found it in the documentation. Row producing procedures > > (and hence Table Procedures) cannot call a procedure.
> Irksome, but better to know about it than not.
Actually, it's a Good Thing (TM).
First, execution of a procedure can entail any number of actions. The
caller must be prepared to deal with any kind of return status.
Where in the SELECT syntax could you accept, much less handle that
status?
Second, SELECT shouldn't have side effects. To avoid them would
be to restrict the procedures that a table-producing procedure might
call to other table-producing procedures.
If table-producing procedures can call other table-producing
procedures, then the system is forced to keep track of which procedures
may call which others, and prevent updates to a called procedure that
change it from being SELECT-only. There's a forest of complexity in
there, and not much benefit, if any, to the user.
Better to follow Nancy Reagan's advice, and Just Say No. :-)
James K. Lowden wrote:
> On Fri, 20 Jan 2012 12:26:08 +0000 (UTC)
> Roy Hann <specia...@processed.almost.meat> wrote:
>> > Aha, just found it in the documentation. Row producing procedures >> > (and hence Table Procedures) cannot call a procedure.
>> Irksome, but better to know about it than not.
> Actually, it's a Good Thing (TM).
> First, execution of a procedure can entail any number of actions. The
> caller must be prepared to deal with any kind of return status.
> Where in the SELECT syntax could you accept, much less handle that
> status?
> Second, SELECT shouldn't have side effects. To avoid them would
> be to restrict the procedures that a table-producing procedure might
> call to other table-producing procedures.
> If table-producing procedures can call other table-producing
> procedures, then the system is forced to keep track of which procedures
> may call which others, and prevent updates to a called procedure that
> change it from being SELECT-only. There's a forest of complexity in
> there, and not much benefit, if any, to the user.
Yeah, I'll buy that.
-- Roy
UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
The latest information is available from www.uk-iua.org.uk.
There is a 'New York Asian Escort' (http://www.nyorientalescort.com)
wholeness about the person who has come to terms with his limitations,
who 'New York Escort' (http://www.nyorientalescort.com) has been brave
enough to let go of his unrealistic dreams and not feel like a failure
for doing so. There is a wholeness 'New York Asian Escorts'
(http://www.nyorientalescort.com) about the man or woman who has learned
that he or she is 'New York Escorts' (http://www.nyorientalescort.com)
to go through a tragedy and survive, he or she can lose someone and
still
