I was recently asked about the ability to run a row producing procedure from anything other than ESQLC, which reminded me of a couple of feature requests that I have recently made http://community.ingres.com/wiki/Table_Procedure_Enhancements, named result columns for a row producing procedure and the ability to use a row producing procedure in a view definition. In addition to these main features the ability to invoke a procedure with positional parameters was added.
I’m currently using 9.3.0 (int.w32/135) and decided to try out these features.
The CREATE PROCEDURE syntax for return a row has been extended to include column names, my clumsy attempt at a partial description of the syntax follows:
<database procedure> ::= CREATE PROCEDURE <procedure name> <procedure parameter list>
RESULT ROW <result row description>
<procedure name> ::= <identifier>
<procedure parameter list> ::= <None> |
<left paren> <SQL parameter declaration list> <right paren>
<result row description> ::= <result row column list> |
<result set identifier> <table procedure column list>
<result set identifier> ::= <identifier>
<result row column list> ::= <left paren> <row column list element>
[ { <comma> <row column list element> }... ] <right paren>
<row column list element> ::= <data type> [ <column constraint> ][ <default> ]
<table procedure column list> ::= <left paren> <table procedure column list element>
[ { <comma> <table procedure column list element> }... ] <right paren>
<table procedure column list element> ::= <column name> <data type> [ <column constraint> ][ <default> ]
The procedure can now be exceuted directly from an EXECUTE PROCEDURE statement (requiring esqlc) or as the table procedure in the FROM clause of a SELECT statement. The latter releasing row producing procedures from the shackles of embedded SQL.
Time for a few simple contrived examples to demonstrate the syntax and use.
A simple table with some data.
create table t1 (c1 integer not null, c2 char(1) not null);
insert into t1 (c1, c2) values (1, 'A');
insert into t1 (c1, c2) values (2, 'B');
insert into t1 (c1, c2) values (2, 'B');
insert into t1 (c1, c2) values (3, 'C');
insert into t1 (c1, c2) values (3, 'C');
insert into t1 (c1, c2) values (3, 'C');
insert into t1 (c1, c2) values (4, 'D');
insert into t1 (c1, c2) values (4, 'D');
insert into t1 (c1, c2) values (4, 'D');
insert into t1 (c1, c2) values (4, 'D');
A monadic procdure that returns a row from the table t1 restricted by value.
create procedure p1 (a1 integer not null)
result row resrow (r1 integer, r2 char(1))
as
declare
ival integer not null;
cval char(1) not null;
begin
for select c1, c2 into :ival, :cval
from t1
where c1 = :a1
do
return row ( :ival, :cval );
endfor;
end
A dyadic procedure that returns a row from table t1 restricted by values.
create procedure p2 (a1 integer not null, a2 char(1) not null)
result row resrow (r1 integer, r2 char(1))
as
declare
ival integer not null;
cval char(1) not null;
begin
for select c1, c2 into :ival, :cval
from t1
where c1 = :a1 and c2 = :a2
do
return row ( :ival, :cval );
endfor;
end
The original syntax to execute the procedure must be executed from esqlc.
# include <stdio.h>
void
main()
{
exec sql begin declare section;
int aval;
int ival;
char array[2];
char *cval = array;
exec sql end declare section;
aval = 2;
exec sql connect test;
exec sql execute procedure p1(a1 = :aval) result row (:ival, :cval);
exec sql begin;
printf("r1 = %d, r2 = %s\n", ival, cval);
exec sql end;
exec sql disconnect;
}
With the feature to execute the procedure in the FROM clause of a SELECT statement you can execute the procedure using
SELECT r1, r2 FROM p1(a1=2);
Parameters can be named, positional or a mixture. Positional parameters must always appear first in the parameter list in a mixture of named and positional parameters.
An example of positional parameters
SELECT r1, r2 FROM p1(2);
SELECT r1, r2 FROM p2(1, 'A');
An example of named parameters
SELECT r1, r2 FROM p1(a1=2);
SELECT r1, r2 from p2(a2='A', a1=1);
An example of mixed parameters
SELECT r1, r2 FROM p2(1, a2='A');
The SELECT query statement that executes the procedure can be used in a VIEW definition and used as usual.
CREATE VIEW v1 AS SELECT r1, r2 FROM p1(2);
Some simple example queries.
-- Aggregate query SELECT COUNT(p1.r1) AS count, SUM(p1.r1) AS sum FROM p1(4); +-------------+----------------------+ |count |sum | +-------------+----------------------+ | 4| 16| +-------------+----------------------+ (1 row) continue Executing . . . -- Query using the view defined from table procedure SELECT t1.*, v1.* FROM t1, v1 WHERE v1.r2='B'; +-------------+----------+-------------+------+ |c1 |c2 |r1 |r2 | +-------------+----------+-------------+------+ | 1|A | 2|B | | 1|A | 2|B | | 2|B | 2|B | | 2|B | 2|B | | 3|C | 2|B | | 3|C | 2|B | | 2|B | 2|B | | 2|B | 2|B | | 3|C | 2|B | | 3|C | 2|B | | 4|D | 2|B | | 4|D | 2|B | | 3|C | 2|B | | 3|C | 2|B | | 4|D | 2|B | | 4|D | 2|B | | 4|D | 2|B | | 4|D | 2|B | | 4|D | 2|B | | 4|D | 2|B | +-------------+----------+-------------+------+ (20 rows) continue Executing . . . -- Query using both table and view SELECT t1.*, v1.* FROM (t1 LEFT JOIN v1 ON t1.c1 = v1.r1 AND v1.r2='B'); +-------------+----------+-------------+------+ |c1 |c2 |r1 |r2 | +-------------+----------+-------------+------+ | 1|A | | | | 2|B | 2|B | | 2|B | 2|B | | 3|C | | | | 2|B | 2|B | | 2|B | 2|B | | 3|C | | | | 4|D | | | | 3|C | | | | 4|D | | | | 4|D | | | | 4|D | | | +-------------+----------+-------------+------+ (12 rows) continue Executing . . . -- Query using both table and table procedure SELECT t1.*, p1.* FROM t1, p1(t1.c1) p1 WHERE p1.r2='B'; +-------------+----------+-------------+------+ |c1 |c2 |r1 |r2 | +-------------+----------+-------------+------+ | 2|B | 2|B | | 2|B | 2|B | | 2|B | 2|B | | 2|B | 2|B | +-------------+----------+-------------+------+ (4 rows)
Post a Comment