Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Stored Procedures and Functions in PostgreSQL

A stored procedure and user-defined function (UDF) is a set of SQL and procedural statements (declarations, assignments, loops, flow-of-control etc.) that stored on the database server and can be invoked using the SQL interface.
Quick Example:
   -- Function increments the input value by 1
   CREATE OR REPLACE FUNCTION increment(i INT) RETURNS INT AS $$
    BEGIN
      RETURN i + 1;
    END;
    $$ LANGUAGE plpgsql;
 
    -- An example how to use the function (Returns: 11)
    SELECT increment(10);
In PostgreSQL, both stored procedures and user-defined functions are created with CREATE FUNCTION statement. There are differences between the notion of stored procedures and functions in database systems:
Stored ProcedureFunction
Use in an expression
Return a value
Return values as OUT parameters
Return a single result set (as a table function)
Return multiple result sets
So in most cases, the purpose of a stored procedure is to:
  • Perform actions without returning any result (INSERT, UPDATE operations i.e.)
  • Return one or more scalar values as OUT parameters
  • Return one or more result sets
Usually the purpose of a user-defined function is to process the input parameters and return a new value.
Reporting Tools
Many reporting tools (Crystal Reports, Reporting Services, BI tools etc.) allow you to specify a query (SQL SELECT statement) or a stored procedure returning a result set to define a data set for a report.
Stored procedures are very useful when you need to perform complex calculations before the data is available for a report.

Stored Procedures in PostgreSQL

Usually stored procedures do not return any value, or return one or more result sets.

No Value Returned

If a stored procedure does not return any value, you can specify void as the return type:
    -- Procedure to insert a new city
    CREATE OR REPLACE FUNCTION add_city(city VARCHAR(70), state CHAR(2)) 
    RETURNS void AS $$
    BEGIN
      INSERT INTO cities VALUES (city, state);
    END;
    $$ LANGUAGE plpgsql;
You can use SELECT statement to invoke the add_city procedure:
   -- Add a new city
   SELECT add_city('St.Louis', 'MO');
You can also use PERFORM add_city() statement to invoke add_city from another procedure or function.

Return a Single Result Set - Return a Cursor

To return a result set from a PostgreSQL procedure, you have to specify refcursor return type, open and return a cursor:
   CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
    DECLARE
      ref refcursor;
    BEGIN
      OPEN ref FOR SELECT city, state FROM cities;
      RETURN ref;
    END;
    $$ LANGUAGE plpgsql;
Important Note: 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).

Comments

Popular posts from this blog

PostgreSQL pgBadger

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL