SQL Dialects Reference/Procedural language/User-defined functions

CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
BEGIN
   /* SQL code */
END

VALUES function_name(...)

or

SELECT function_name(...) FROM ... WHERE function_name(...) = ...

N/A

N/A

CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
BEGIN
   /* SQL code */
END

SELECT function_name(...) FROM ... WHERE function_name(...) = ...

DELIMITER $$

CREATE FUNCTION function_name
  (input_parameter_name datatype, ... )
RETURNS datatype
BEGIN
RETURN
  /* SQL code */
END$$

DELIMITER ;

SELECT function_name(...)

CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS datatype
AS $$
DECLARE
  variable_name datatype;
BEGIN
  /* SQL code */
END;
$$ LANGUAGE plpgsql;

SELECT function_name(...)

UDFs are written in external tools and compiled into executable form.
They are dynamically loaded at runtime.
In order for server to pick them up, they need to be registered, like this:

DECLARE EXTERNAL FUNCTION function_name [datatype, ...]
RETURNS datatype
ENTRY_POINT 'entryname'
MODULE_NAME 'modulename';

SELECT function_name(...)

CREATE OR REPLACE my_function
(p_contract IN VARCHAR2, p_org_id IN VARCHAR2)
RETURN DATE
AS
l_ret_eff_date date;

if ....

return l_ret_eff_date;

SELECT my_function('PARM1', 'ORG1') ....