• VOLATILE (default)
  • Each call can return a different result
  • Example: random() or timeofday()
  • Functions modifying table contents must be declared volatile
• STABLE
  • Returns same result for same arguments within single query
    Example: now()
  • Consider configuration settings that affect output
• IMMUTABLE
  • Always returns the same result for the same arguments
    Example: lower('ABC')
  • Unaffected by configuration settings
  • Not dependent on table contents

There is no procedure and all functions in PostgreSQL, Enterprise DB does support CREATE PROCEDURE to be compatible with Oracle.

For all intents and purposes, PostgreSQL has less of a need for CREATE PROCEDURE than other databases aside from looking more like other databases.

Now let us create a function

Now let us call this function using PHP query tool.

 To distinguish, a procedure from a function, that the stored procedures can usually multiple results sets where as functions can not is to return multiple result sets. In PostgreSQL such tasks are accomplished by creating a function that returns a set of refcursors.

In PostgreSQL RDBMS, the cursor remains open until the end of transaction, and since PostgreSQL works in auto-commit mode by default, the cursor is closed immediately after the procedure call, so it is not available to the caller. To work with cursors you have to start a transaction (turn auto-commit off).

 In PostgreSQL, you can write a function marked as VOLATILE ,meaning that the function value can change even within a single table scan that updates data, and you intend to get  different output on successive calls.

CREATE OR REPLACE FUNCTION cfcal(i numeric)
RETURNS numeric AS
$BODY$
DECLARE
temp1 numeric ;
BEGIN
/*select cfcal(i+40) INTO temp1;*/
RETURN (i * 9/5) + 32 ;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION cfcal(numeric)
OWNER TO postgres;