PowerPoint Presentation

Professor: Dr. Shu-Ching ChenTA: Haiman TianStored Procedure used in PosgreSQLWhat are stored proceduresA subroutine available to applications that access a relational database system.PL/pgSQL : A loadable procedural language.Creates functions and trigger proceduresAdds control structuresPerforms complex computationInherits all user-defined types, functionsCan be defined to be trusted by the serverEasy to use

PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural language that can be used to create functions and trigger procedures, adds control structures to the SQL language, can perform complex computations, inherits all user-defined types, functions, and operators, can be defined to be trusted by the server, is easy to use.

Astored procedureis asubroutineavailable to applications that access arelationaldatabase system. Typical uses for stored procedures includedata validation(integrated into the database) oraccess controlmechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of severalSQLstatements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another.Stored procedures are similar touser-defined functions(UDFs).

2Why do we need stored procedureOne QueryWait, receive, process/computeDatabase ServerInternetReduce roundtrips across the networkCan make security easier to manageAre precompiledReduce roundtrips across the network- Stored procedures take zero or more input parameters, do something, and return a result, much like a function in any language. By black boxing that functionality on the database server, we avoid the need to move large datasets across the wire to the application server for processing. This can decrease network utilization and application latency.

Can make security easier to manage- While views can help simplify the permissions needed for complex queries, stored procedures make it even easier. Instead of giving a user/application the correct rights to specific tables (or columns), a person only needs execution rights to the stored procedure.

Are precompiled- Stored procedures are stored in a precomplied state on the server, meaning that the query optimizer doesnt have to recalculate the most efficient execution path each time the query is run. This reduces server overhead and can speed things up a bit.

3Structure of PL/pgSQL

PL/pgSQL code is organized in blocks of code. This method of organization is known asblock structured code. Code blocks are entered within a SQLCREATE FUNCTIONcall that creates the PL/pgSQL function in the PostgreSQL database. ThisCREATE FUNCTIONcommand names the new function, states its argument types, and states the return type. The function's main code block then starts with a declaration section.

All variables are declared and optionally initialized to a default value in the declaration section of a code block. A variable declaration specifies the variable's name and type. The declaration section is denoted by the DECLAREkeyword. Each variable declaration is ended with a semicolon.

After declaring variables, the main body of the code block is started with theBEGINkeyword. The code block's statements should appear after theBEGINkeyword.TheENDkeyword designates the end of the code block. The main block of a PL/pgSQL function should return a value of its specified return type and end any sub-blocks (code blocks started within another code block) before itsENDkeyword is reached.

4Declarations (1)Declaring PL/pgSQL variable

Declarations (2)Declaring PL/pgSQL variable and assigning values

The NOT NULL keywords indicate that a variable cannot be set as NULL.The DEFAULT keyword allows you to provide a default value for a variable. Alternatively, you can use the := operator without specifying the DEFAULT keyword, to the same effect.

6Declarations (3)Declaring Function Parameters(1) directly give a name to the parameter in the command

(2) name ALIAS FOR $n;

7Declarations (4)Directly using argument variables

Declarations (5)Attributes%TYPE attribute

Declarations (6)Attributes%ROWTYPE attribute

Comment syntax Single-line comments

Block comments

Single line comments begin with two dashes (--) and have no end-character. The parser interprets all characters on the same line after the two dashes as part of the comment.

The second type of comment is the multiline orblockcomment, which should be familiar to most anyone who has worked with programming languages before. Block comments begin with the forward slash and asterisk characters (/*) and end with the asterisk and forward slash characters (*/). Block comments can span multiple lines, and any text between the opening/*and closing*/is considered a comment.Example 11-7shows the correct usage of a block comment.

11Basic Statements (1)Assignment

Executing a Command with NO RESULT PERFORM

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

If theSTRICToption is specified, the query must return exactly one row or a run-time error will be reported, eitherNO_DATA_FOUND(no rows) orTOO_MANY_ROWS(more than one row). You can use an exception block if you wish to catch the error, for example:

where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields

If STRICT is not specified in the INTO clause, then target will be set to the first row returned by the query, or to nulls if the query returned no rows. (Note that "the first row" is not well-defined unless you've used ORDER BY.) Any result rows after the first row are discarded.13Basic Statements (3)Example

Basic Statements (4)

Basic Statements (5)FOUND Boolean variable

Use the specialFOUNDBoolean variable directly after aSELECT INTOstatement to check whether or not the statement successfully inserted a value into the specified variable16Control Structures(1)RETURN expression

Control Structures(2)IF statementsIF THENIF THEN ELSEIF THEN ELSIF THEN ELSE

Control Structures(3)CASE statementsCASE WHEN THEN ELSE END CASECASE WHEN THEN ELSE END CASE

Control Structures(4)LOOP

EXIT

LOOPdefines an unconditional loop that is repeated indefinitely until terminated by anEXITorRETURNstatement. The optionallabelcan be used byEXITandCONTINUEstatements within nested loops to specify which loop those statements refer to.

If nolabelis given, the innermost loop is terminated and the statement followingEND LOOPis executed next. Iflabelis given, it must be the label of the current or some outer level of nested loop or block. Then the named loop or block is terminated and control continues with the statement after the loop's/block's correspondingEND.IfWHENis specified, the loop exit occurs only ifboolean-expressionis true. Otherwise, control passes to the statement afterEXIT.EXITcan be used with all types of loops; it is not limited to use with unconditional loops.When used with aBEGINblock,EXITpasses control to the next statement after the end of the block. Note that a label must be used for this purpose; an unlabelledEXITis never considered to match aBEGINblock. 20Control 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

If no error occurs, this form of block simply executes all thestatements, and then control passes to the next statement afterEND. But if an error occurs within thestatements, further processing of thestatementsis abandoned, and control passes to theEXCEPTIONlist. The list is searched for the firstconditionmatching the error that occurred. If a match is found, the correspondinghandler_statementsare executed, and then control passes to the next statement afterEND. If no match is found, the error propagates out as though theEXCEPTIONclause were not there at all: the error can be caught by an enclosing block withEXCEPTION, or if there is none it aborts processing of the function.24Cursors (1)Declaring Cursor Variables

OPEN FOR query

This provides an efficient way to return large row sets from functions.

Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time

create a cursor variable is just to declare it as a variable of type refcursor

All three of these variables have the data type refcursor, but the first can be used with any query, while the second has a fully specified query already bound to it, and the last has a parameterized query bound to it.

Before a cursor can be used to retrieve rows, it must beopened. (This is the equivalent action to the SQL commandDECLARE CURSOR.)PL/pgSQLhas three forms of theOPENstatement, two of which use unbound cursor variables while the third uses a bound cursor variable.25Cursors (2)Using CursorsFETCH

MOVE

NEXT PRIORFIRSTLASTABSOLUTE countRELATIVE countFORWARDBACKWORD

NEXTFetch the next row. This is the default ifdirectionis omitted.PRIORFetch the prior row.FIRSTFetch the first row of the query (same asABSOLUTE 1).LASTFetch the last row of the query (same asABSOLUTE -1).ABSOLUTEcountFetch thecount'th row of the query, or theabs(count)'th row from the end ifcountis negative. Position before first row or after last row ifcountis out of range; in particular,ABSOLUTE 0positions before the first row.RELATIVEcountFetch thecount'th succeeding row, or theabs(count)'th prior row ifcountis negative.RELATIVE 0re-fetches the current row, if any.

26Cursors (3)Using CursorsCLOSE

Returning Cursor

CLOSEcloses the portal underlying an open cursor. This can be used to release resources earlier than end of transaction, or to free up the cursor variable to be opened again27Cursors (4)Looping Through a Cursors Result

The cursor variable must have been bound to some query when it was declared, and itcannotbe open already. TheFORstatement automatically opens the cursor, and it closes the cursor again when the loop exits. A list of actual argument value expressions must appear if and only if the cursor was declared to take arguments. These values will be substituted in the query, in just the same way as during anOPEN.

The variablerecordvaris automatically defined as typerecordand exists only inside the loop (any existing definition of the variable name is ignored within the loop). Each row returned by the cursor is successively assigned to this record variable and the loop body is executed.28Errors and MessagesRAISE

Example

ReferencePostgreSQL Manuals PostgreSQL 9.1http://www.postgresql.org/docs/9.1/static/index.htmlPractical PostgreSQLhttp://www.faqs.org/docs/ppbook/c19610.htmCOST in stored procedures: A positive number giving the estimated execution cost for thefunction, in units of cpu_operator_cost. If the function returns aset, this is the cost per returned row. If the cost is not specified,1 unit is assumed for C-language and internal functions, and 100 unitsfor functions in all other languages. Larger values cause the plannerto try to avoid evaluating the function more often than necessary. "

30

Stored Procedure in PgAdmin123

Stored Procedure in PgAdmin