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
      RETURN i + 1;
    $$ 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 $$
      INSERT INTO cities VALUES (city, state);
    $$ 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 $$
      ref refcursor;
      OPEN ref FOR SELECT city, state FROM cities;
      RETURN ref;
    $$ 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).


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

spfile and pfile errors

2017 Shortern URL - Other Website Like ADFOC.US

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory