Table Procedure
A table procedure is a row-producing database procedure that can be invoked in the FROM clause of a SELECT statement.
A table procedure reference uses the following syntax:
proc_name ([param_name=]param_spec {,[param_name=]param_spec})
The parameter expressions can reference columns from other tables or views in the FROM clause, effectively representing joins between the tables and the table procedures. Columns of the result rows of the table procedures can also be referenced anywhere in the query.
The following query example shows the table procedure tproc1() with the correlation name tp.
SELECT a.c1, b.d1, tp.r5 FROM table1 a, table2 b, tproc1 (p1 = 19, p2 = 'pqr',
p3 = a.c2) tp
WHERE a.c4 = b.d3 AND b.d6 = tp.r2;
Its parameter list contains a reference to column c2 of table1 and the WHERE clause has an equijoin between columns d6 of table2 and the result column r2 of tproc1().
The following query example shows the creation of a view from a table procedure in which some, but not all, parameters are specified:
CREATE VIEW tpv1 AS SELECT * FROM tproc2 (p3 = 25);
Table Procedure Restrictions
The parameter list of a table procedure can reference columns from tables in the FROM clause or result columns of another table procedure in the FROM clause. This effectively represents a join between the tables and table procedures with one set of result rows being produced by the table procedure for each set of column values referenced in its parameter list. However, if parameter lists of table procedures reference result columns of other table procedures, they must not form a cycle.
For example, the following is not permitted
FROM tproc1(p1 = 25, p2 = tp2.rc1) tp1, tproc2(q1 = tp1.rc4, q2 = 'abc') tp2, ...
because the tproc1 parameter list references a result column of tproc2 and the tproc2 parameter list references a result column from tproc1.
Note: The LOB result column of a table procedure cannot be referenced in a query. A syntax error will result.
Table Procedure Example
Table procedures are useful for programmatically performing selections from various tables.
For example:
INGRES TERMINAL MONITOR Copyright 2009 Ingres Corporation
Ingres Microsoft Windows Version II 9.3.0 (int.w32/155) login
Thu Sep 24 23:12:56 2009
continue
* go
* * * * * * * * *
/*
** Table Procedure Test Case
*/
/* Drop Procedure
*/
drop procedure tblproc;
commit;
Executing . . .
continue
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
/*
** Create Table Procedure
*/
create procedure tblproc (id integer not null)
result row( char(32) not null, char(32) not null)
as
declare id2 integer not null;
col1 char(64) not null;
col2 char(32) not null;
begin
if (id = 0) then
for select first 5 cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do return row (:col1, :col2);
endfor;
elseif (id = 1) then
for select first 5 table_owner, table_name into :col1, :col2
from "$ingres".iitables do
return row (:col1, :col2);
endfor;
elseif (id = 2) then
for select first 5 trim(table_owner)+'.'+trim(table_name), column_name into :col1, :col2
from "$ingres".iicolumns do
return row (:col1, :col2);
endfor;
elseif (id = 3) then
for select first 5 cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do
return row (:col1, :col2);
endfor;
for select first 5 table_owner, table_name into :col1, :col2
from "$ingres".iitables do
return row (:col1, :col2);
endfor;
for select first 5 trim(table_owner)+'.'+trim(table_name), column_name into :col1, :col2
from "$ingres".iicolumns do
return row (:col1, :col2);
endfor;
else
for select cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do
return row (:col1, :col2);
endfor;
endif;
end
Executing . . .
* * ;
commit;
Executing . . .
continue
* * * * * * *
/*
** Get help on created procedure
*/
help procedure tblproc;
Executing . . .
Procedure: tblproc
Owner: ingres
Procedure Type: native
Object type: user object
Created: 24-sep-2009 23:12:56
Procedure Definition:
/* 1 */ create procedure tblproc (id integer not null) result row(
char(32) not null, char(32) not null) as declare id2 integer not null;
/* 2 */ col1 char(64) not null;
/* 3 */ col2 char(32) not null;
/* 4 */ begin if (id = 0) then for select first 5 cap_capability,
cap_value into :col1, :col2 from "$ingres".iidbcapabilities do return
row (:col1, :col2);
/* 5 */ endfor;
/* 6 */ elseif (id = 1) then for select first 5 table_owner,
table_name into :col1, :col2 from "$ingres".iitables do return row
(:col1, :col2);
/* 7 */ endfor;
/* 8 */ elseif (id = 2) then for select first 5 trim(table_owner)+
'.'+trim(table_name), column_name into :col1, :col2 from
"$ingres".iicolumns do return row (:col1, :col2);
/* 9 */ endfor;
/* 10 */ elseif (id = 3) then for select first 5 cap_capability,
cap_value into :col1, :col2 from "$ingres".iidbcapabilities do return
row (:col1, :col2);
/* 11 */ endfor;
/* 12 */ for select first 5 table_owner, table_name into :col1,
:col2 from "$ingres".iitables do return row (:col1, :col2);
/* 13 */ endfor;
/* 14 */ for select first 5 trim(table_owner)+'.'+trim(table_name),
column_name into :col1, :col2 from "$ingres".iicolumns do return row
(:col1, :col2);
/* 15 */ endfor;
/* 16 */ else for select cap_capability, cap_value into :col1, :col2
from "$ingres".iidbcapabilities do return row (:col1, :col2);
/* 17 */ endfor;
/* 18 */ endif;
/* 19 */ end
continue
* * * * * * *
/*
** test cases
*/
select * from tblproc();
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|QUEL_LEVEL |II9.3.0 |
|SQL_LEVEL |II9.3.0 |
|DISTRIBUTED |N |
|MIXEDCASE_NAMES |N |
|INGRES |Y |
+--------------------------------+--------------------------------+
(5 rows)
continue
* * select * from tblproc(id=1);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|$ingres |iiprotect |
|$ingres |iidbcapabilities |
|$ingres |iiotables |
|$ingres |ii_fields |
|$ingres |iirule |
+--------------------------------+--------------------------------+
(5 rows)
continue
* * select * from tblproc(id=2);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|$ingres.iirelation |relfree |
|$ingres.iirelation |relnparts |
|$ingres.iirelation |relmax |
|$ingres.iirelation |relcreate |
|$ingres.iirelation |relsave |
+--------------------------------+--------------------------------+
(5 rows)
continue
* * select * from tblproc(id=3);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|QUEL_LEVEL |II9.3.0 |
|SQL_LEVEL |II9.3.0 |
|DISTRIBUTED |N |
|MIXEDCASE_NAMES |N |
|INGRES |Y |
|$ingres |iiprotect |
|$ingres |iidbcapabilities |
|$ingres |iiotables |
|$ingres |ii_fields |
|$ingres |iirule |
|$ingres.iirelation |relfree |
|$ingres.iirelation |relnparts |
|$ingres.iirelation |relmax |
|$ingres.iirelation |relcreate |
|$ingres.iirelation |relsave |
+--------------------------------+--------------------------------+
(15 rows)
continue
* * select * from tblproc(id=99);
Executing . . .
+--------------------------------+--------------------------------+
|result_column0 |result_column1 |
+--------------------------------+--------------------------------+
|QUEL_LEVEL |II9.3.0 |
|SQL_LEVEL |II9.3.0 |
|DISTRIBUTED |N |
|MIXEDCASE_NAMES |N |
|INGRES |Y |
|INGRES/SQL_LEVEL |00930 |
|COMMON/SQL_LEVEL |00930 |
|INGRES/QUEL_LEVEL |00930 |
|SAVEPOINTS |Y |
|DBMS_TYPE |INGRES |
|PHYSICAL_SOURCE |T |
|MAX_COLUMNS |1024 |
|INGRES_RULES |Y |
|INGRES_UDT |Y |
|INGRES_AUTH_GROUP |Y |
|INGRES_AUTH_ROLE |Y |
|INGRES_LOGICAL_KEY |Y |
|UNIQUE_KEY_REQ |N |
|ESCAPE |Y |
|OWNER_NAME |QUOTED |
|STANDARD_CATALOG_LEVEL |00930 |
|OPEN/SQL_LEVEL |00904 |
|DB_NAME_CASE |LOWER |
|DB_DELIMITED_CASE |LOWER |
|DB_REAL_USER_CASE |LOWER |
|NATIONAL_CHARACTER_SET |N |
|SQL_MAX_BYTE_LITERAL_LEN |32000 |
|SQL_MAX_CHAR_LITERAL_LEN |32000 |
|SQL_MAX_BYTE_COLUMN_LEN |32000 |
|SQL_MAX_VBYT_COLUMN_LEN |32000 |
|SQL_MAX_CHAR_COLUMN_LEN |32000 |
|SQL_MAX_VCHR_COLUMN_LEN |32000 |
|SQL_MAX_NCHR_COLUMN_LEN |16000 |
|SQL_MAX_NVCHR_COLUMN_LEN |16000 |
|SQL_MAX_SCHEMA_NAME_LEN |32 |
|SQL_MAX_TABLE_NAME_LEN |32 |
|SQL_MAX_COLUMN_NAME_LEN |32 |
|SQL_MAX_USER_NAME_LEN |32 |
|SQL_MAX_ROW_LEN |262144 |
|SQL_MAX_STATEMENTS |0 |
|SQL_MAX_DECIMAL_PRECISION |39 |
+--------------------------------+--------------------------------+
(41 rows)
continue
* *
Ingres Version II 9.3.0 (int.w32/155) logout
Thu Sep 24 23:12:56 2009