Function Trigger used in Posgre SQL Professor Dr

Function, Trigger used in Posgre. SQL Professor: Dr. Shu-Ching Chen TA: Hsin-Yu Ha

What are stored procedures �A subroutine available to applications that access a relational database system. �PL/pg. SQL : A loadable procedural language. �Creates functions and trigger procedures �Adds control structures �Performs complex computation �Inherits all user-defined types, functions �Can be defined to be trusted by the server �Easy to use

Why do we need stored procedure One Query Internet Database Server Wait, receive, process/compute �Reduce roundtrips across the network �Can make security easier to manage �Are precompiled

Structure of PL/pg. SQL

Declarations (1) �Declaring PL/pg. SQL variable

Declarations (2) �Declaring PL/pg. SQL variable and assigning values

Declarations (3) �Declaring Function Parameters (1) directly give a name to the parameter in the command (2) name ALIAS FOR $n;

Declarations (4) �Directly using argument variables

Declarations (5) �Attributes �%TYPE attribute

Declarations (6) �Attributes �%ROWTYPE attribute

Comment syntax �Single-line comments �Block comments

Basic Statements (1) �Assignment �Executing a Command with NO RESULT – PERFORM

Basic Statements (2) �Executing a Command with a Single-row result

Basic Statements (3) �Example

Basic Statements (4)

Basic Statements (5) �FOUND – Boolean variable

Control Structures(1) �RETURN expression

Control Structures(2) �IF statements �IF … THEN … ELSE

Control Structures(3) �CASE statements �CASE … WHEN … THEN … ELSE … END CASE �CASE WHEN … THEN … ELSE … END CASE

Control Structures(4) �LOOP �EXIT

Control Structures(5) �CONTINUE �WHILE

Control Structures(6) �FOR (Integer Variant)

Control Structures(7) �FOR (Looping through query results)

Control Structures(8) �Trapping Errors � http: //www. postgresql. org/docs/9. 1/static/errcodes- appendix. html#ERRCODES-TABLE

before constraints are checked and the INSERT, UPDATE, or DELETE is attempted Trigger(1) after constraints are checked and the INSERT, UPDATE, or DELETE has completed in the case of inserts, updates or deletes on a view Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views. BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT.

Trigger(2) Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views. BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT.

Trigger(3) SELECT does not modify any rows so you cannot create SELECT triggers. Rules and views are more appropriate in such cases. In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row. Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them since the condition cannot refer to any values in the table.

Trigger(4) Execute the function check_account_update whenever a row of the table accounts is about to be updated: The same, but only execute the function if column balance is specified as a target in the UPDATE command: This form only executes the function if column balance has in fact changed value:

Trigger(5) Call a function to log updates of accounts, but only if something changed: Execute the function view_insert_row for each row to insert rows into the tables underlying a view:

Trigger(6) DROP TRIGGER removes an existing trigger definition. : ALTER TRIGGER changes properties of an existing trigger. The RENAME clause changes the name of the given trigger without otherwise changing the trigger definition.

Trigger(7) One built in trigger function, suppress_redundant_updates_trigger, which will prevent any update that does not actually change the data in the row from taking place, in contrast to the normal behaviour which always performs the update regardless of whether or not the data has changed. The suppress_redundant_updates_trigger function can be added to a table like this:

Cursors (1) �Declaring Cursor Variables �OPEN FOR query

Cursors (2) �Using Cursors �FETCH �MOVE NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count FORWARD BACKWORD

Cursors (3) �Using Cursors �CLOSE �Returning Cursor

Cursors (4) �Looping Through a Cursor’s Result

Errors and Messages �RAISE �Example

Reference �Postgre. SQL Manuals Postgre. SQL 9. 1 �http: //www. postgresql. org/docs/9. 1/static/index. html �Practical Postgre. SQL �http: //www. faqs. org/docs/ppbook/c 19610. htm

Stored Procedure in Pg. Admin 3 2 1

Stored Procedure in Pg. Admin