EnterpriseDB: Function Syntax
Oracle Tips by
CREATE OR REPLACE FUNCTION <function name>[(parameter
RETURNS <data type> AS $$
<program body in language of choice as a
$$ LANGAUGE <language name> [STRICT];
not be providing many examples or an in depth treatise on the
languages below. There are many references on the Internet and
there are some very good books that cover this subject in great
detail. I will provide additional examples of these languages in
Chapter 8, Oracle Compatibility.
looks an amazing amount like Oracle's PL/SQL but is not nearly as
robust. PL/pgSQL is also a block oriented language like
SPL. The structure of a PL/pgSQL block is: optional declaration
section, executable section, and optional exception section.
language also accepts named and typed parameters. Parameters can
be input or output. Parameters can be any data type including
composites and arrays.
fully supports all of the expected conditional and flow control
structures. Variable declaration is straight forward.
most part, you will not program in PL/pgSQL but you may find useful,
pre-written functionality available that is written in PL/pgSQL.
If you can write it in PL/pgSQL, you can write it in SPL and, to
maintain Oracle compatibility, you should concentrate your code in the
a simple PL/pgSQL function that returns an employee name based on a
passed in number:
CREATE OR REPLACE FUNCTION get_emp_names(p_empno
integer) RETURNS text AS $$
WHERE empno = p_empno;
RAISE EXCEPTION 'EMP NOT FOUND';
$$ LANGUAGE plpgsql;
call this function from another PL/pgSQL function, from SPL or from
SELECT get_emp_names(1) FROM DUAL;
ERROR: EMP NOT FOUND
SELECT get_emp_names(7369) FROM DUAL;
it from an SPL function might look like this:
CREATE OR REPLACE FUNCTION get_emp_names3(p_empno
number) RETURN varchar2 AS
v_ename := get_emp_names(cast(p_empno as
Employee Found for EMPNO: ' || to_char(p_empno) );
things to notice here is that I used CAST(p_empno as integer) to
ensure that the function signature of the PL/pgSQL function matched
the SPL call. I will cover this function in more detail in
other thing to notice is that I used an SPL exception handler to catch
and handle the PL/pgSQL exception that was raised. When calling
this function, an error is displayed instead of having an exception
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.