Syntax:
CREATE PROCEDURE procname
[(<inparam> [, <inparam> ...])]
[RETURNS (<outparam> [, <outparam> ...])]
AS
[<declarations>]
BEGIN
[<PSQL statements>]
END
<inparam> ::= <param_decl> [{= | DEFAULT} value]
<outparam> ::= <param_decl>
<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]
<type> ::= sql_datatype | [TYPE OF] domain
<declarations> ::= See PSQL::DECLARE for the exact syntax
/* If sql_datatype is a string type, it may include a character set */
Example:
create domain bool3
smallint
check (value is null or value in (0,1));
create domain bigposnum
bigint
check (value >= 0);
/* Determines if A is a multiple of B: */
set term #;
create procedure ismultiple (a bigposnum, b bigposnum)
returns (res bool3)
as
declare ratio type of bigposnum; -- ratio is a bigint
declare remainder type of bigposnum; -- so is remainder
begin
if (a is null or b is null) then res = null;
else if (b = 0) then
begin
if (a = 0) then res = 1; else res = 0;
end
else
begin
ratio = a / b; -- integer division!
remainder = a - b*ratio;
if (remainder = 0) then res = 1; else res = 0;
end
end#
set term ;#Example:
create domain bool3
smallint
check (value is null or value in (0,1));
create domain bigposnum
bigint
check (value >= 0);
/* Determines if A is a multiple of B: */
set term #;
create procedure ismultiple (a bigposnum, b bigposnum)
returns (res bool3)
as
declare ratio type of bigposnum; -- ratio is a bigint
declare remainder type of bigposnum; -- so is remainder
begin
if (a is null or b is null) then res = null;
else if (b = 0) then
begin
if (a = 0) then res = 1; else res = 0;
end
else
begin
ratio = a / b; -- integer division!
remainder = a - b*ratio;
if (remainder = 0) then res = 1; else res = 0;
end
end#
set term ;#