Pyrrho supports stored procedures and functions following the SQL2003 syntax (volumes 2 and 4). The programming model offered in this way is computationally complete, so the use of external code written in other programming languages is not supported.
In SQL2003 the syntax :v is not supported for variable references, and instead variables are identified by qualified identifier chains of form a.b.v . The syntax ? for parameters is not supported either.
In SQL2003-2-11.50 we see that procedures never have a returns clause (functions should be used if a value is to be returned), and procedure parameters can be declared IN, OUT or INOUT and can be RESULT parameters. Variables can be ROW types and collection types. For functions, TABLE is a valid RETURNS type (it is strictly speaking a “multiset” in SQL2003 terminology). From SQL2003-2-6.39 we see that RETURN TABLE ( queryexpression ) is a valid return statement.
Here are some outlines of procedure statements specified in SQL2003-4 and supported in Pyrrho. Complete syntax summaries for Pyrrho are given in chapter 7.
create table author(id int primary key, aname char)
create table book(id int primary key, authid int, title char)
[ create function booksby(auth char) returns table(title char)
return table(select title from author a inner join book b on a.id = b.authid where aname = booksby.auth ) ]
This example also shows that a routine body is a single procedure statement (possibly a compound BEGIN..END statement). Square brackets are used like this in the Pyrrho client utilities to enclose multi-line SQL statements.
The above function can be referenced by statements such as
select * from table(booksby('Charles Dickens'))
The keyword table in this example is required by SQL2003-2(7.6).
The following functions perform the same task. The first uses a handler, while the second uses a for statement.