Documentation: Manuals: PostgreSQL 8.4: The Rule System. This chapter discusses the rule system in PostgreSQL. Production rule systems are conceptually simple, but there are many subtle points involved in actually using them. Some other database systems define active database rules, which are usually stored procedures and triggers. In PostgreSQL, these can be implemented using functions and triggers as well. The rule system (more precisely speaking, the query rewrite rule system) is totally different from stored procedures and triggers. It modifies queries to take rules into consideration, and then passes the modified query to the query planner for planning and execution. PostgreSQL 8.4: CREATE RULE. Name CREATE RULE -- define a new rewrite rule Synopsis CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } Description CREATE RULE defines a new rule applying to a specified table or view.
The PostgreSQL rule system allows one to define an alternative action to be performed on insertions, updates, or deletions in database tables. Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT command. You can create the illusion of an updatable view by defining ON INSERT, ON UPDATE, and ON DELETE rules (or any subset of those that's sufficient for your purposes) to replace update actions on the view with appropriate updates on other tables. There is a catch if you try to use conditional rules for view updates: there must be an unconditional INSTEAD rule for each action you wish to allow on the view. Parameters name The name of a rule to create. PostgreSQL 8.0: Control Stru. Control structures are probably the most useful (and important) part of PL/pgSQL.
With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very flexible and powerful way. 35.7.1. Returning From a Function There are two commands available that allow you to return data from a function: RETURN and RETURN NEXT. RETURN expression; RETURN with an expression terminates the function and returns the value of expression to the caller. When returning a scalar type, any expression can be used. The return value of a function cannot be left undefined.
If you have declared the function to return void, a RETURN statement must still be provided; but in this case the expression following RETURN is optional and will be ignored if present. RETURN NEXT expression; When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. Functions that use RETURN NEXT should be called in the following fashion: SELECT * FROM some_func(); 35.7.2. Example: 35.7.3.
PostgreSQL 8.3: Errors and M. Use the RAISE statement to report messages and raise errors. RAISE level 'format' [, expression [, ...]]; Possible levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. Inside the format string, % is replaced by the next optional argument's string representation. In this example, the value of v_job_id will replace the % in the string: RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; This example will abort the transaction with the given error message: RAISE EXCEPTION 'Nonexistent ID --> %', user_id; RAISE EXCEPTION presently always generates the same SQLSTATE code, P0001, no matter what message it is invoked with.
PostgreSQL 8.0: PostgreSQL E. All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard's conventions for "SQLSTATE" codes. Applications that need to know which error condition has occurred should usually test the error code, rather than looking at the textual error message. The error codes are less likely to change across PostgreSQL releases, and also are not subject to change due to localization of error messages.
Note that some, but not all, of the error codes produced by PostgreSQL are defined by the SQL standard; some additional error codes for conditions not defined by the standard have been invented or borrowed from other databases. According to the standard, the first two characters of an error code denote a class of errors, while the last three characters indicate a specific condition within that class. Thus, an application that does not recognize the specific error code may still be able to infer what to do from the error class.
Table A-1. PostgreSQL 8.1: Trigger Proc. PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER — trigger arguments are passed via TG_ARGV, as described below. When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers. Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers.
Data type name; variable that contains the name of the trigger actually fired. Data type text; a string of either BEFORE or AFTER depending on the trigger's definition. Example 36-2. Example 36-3. Example 36-4. PostgreSQL 8.1: Porting from. This section explains differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port applications from Oracle® to PostgreSQL. PL/pgSQL is similar to PL/SQL in many aspects. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, conditionals are similar. The main differences you should keep in mind when porting from PL/SQL to PL/pgSQL are: 36.11.1. Example 36-5 shows how to port a simple function from PL/SQL to PL/pgSQL. Example 36-5. Here is an Oracle PL/SQL function: CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, v_version varchar) RETURN varchar IS BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; / show errors; Let's go through this function and see the differences compared to PL/pgSQL: The RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL.
Example 36-6. This is the Oracle version: