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:
So in most cases, the purpose of a stored procedure is to:
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.
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 Procedure | Function | |
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 |
- 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
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
Post a Comment