PostgreSQL Stored Procedures Part 1

Stored procedures are a great way to enforce business and data access logic in a complex environment. Often with larger projects you will have multiple applications architected by a varied set of professionals all interacting with the same data sets.   Stored procedures are a tried and true way to regain some control over those data sets and ensure standards compliance.

The following paragraphs will show the basic syntax and some simple examples for using stored procedures in PostgreSQL.

Syntax

CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
[ RETURNS rettype ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

Examples (generic)

Here are some trivial examples to help you get started. For more information and examples, see Section 34.3.

  1. CREATE FUNCTION ADD(integer, integer) RETURNS integer
  2.     AS ‘select $1 + $2;’
  3. LANGUAGE SQL
  4. IMMUTABLE
  5. RETURNS NULL ON NULL INPUT;

Increment an integer, making use of an argument name, in PL/pgSQL:

  1. CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
  2.         BEGIN
  3.                 RETURN i + 1;
  4.        END;
  5. $$ LANGUAGE plpgsql;

Return a record containing multiple output parameters:

  1. CREATE FUNCTION dup(IN int, out f1 int, out f2 text)
  2.     AS $$ SELECT $1, CAST($1 AS text) || ‘ is text’ $$
  3.     LANGUAGE SQL;
  4.  
  5. SELECT * FROM dup(42);

You can do the same thing more verbosely with an explicitly named composite type:

  1. CREATE TYPE dup_result AS (f1 int, f2 text);
  2.  
  3. CREATE FUNCTION dup(int) RETURNS dup_result
  4.     AS $$ SELECT $1, CAST($1 AS text) || ‘ is text’ $$
  5.     LANGUAGE SQL;
  6.  
  7. SELECT * FROM dup(42);

Resources

http://www.postgresql.org/docs/8.3/static/sql-createfunction.html

Related posts:

  1. PostgreSQL Stored Procedures – Part 2
  2. PostgreSQL Introduction
  3. PHP Puts the Un in Unset
  4. PostgreSQL Cheat Sheet
  5. Postgres::Handler
0 Comment   |   Posted in Database,PostgreSQL,SQL,blog by lcleveland on November 10, 2009

Leave a comment










Search Articles

Article Categories

Tags

Recent Articles