From f514bb3e2586d3896f4584b9b345f9565bef1f8a Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 30 Jul 2002 19:36:13 +0000 Subject: Would it be wise to have the plsql.sgml file renamed to plpgsql.sgml? All of the internal tags are of the latter. The other thing I noticed is that most of the quick examples in the file use a para and synopsis. Is there a reason we're not using ? Rod Taylor --- doc/src/sgml/filelist.sgml | 4 +- doc/src/sgml/plpgsql.sgml | 3010 ++++++++++++++++++++++++++++++++++++++++++++ doc/src/sgml/plsql.sgml | 3001 ------------------------------------------- 3 files changed, 3012 insertions(+), 3003 deletions(-) create mode 100644 doc/src/sgml/plpgsql.sgml delete mode 100644 doc/src/sgml/plsql.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index d8c5998fb84..d6fdd71561b 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,4 +1,4 @@ - + @@ -83,7 +83,7 @@ - + diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml new file mode 100644 index 00000000000..9a58e6b8e3b --- /dev/null +++ b/doc/src/sgml/plpgsql.sgml @@ -0,0 +1,3010 @@ + + + + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language + + + PL/pgSQL + + + + PL/pgSQL is a loadable procedural language for the + PostgreSQL database system. + + + + This package was originally written by Jan Wieck. This + documentation was in part written + by Roberto Mello (rmello@fslc.usu.edu). + + + + Overview + + + 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. + + + + + + The PL/pgSQL call handler parses the function's source text and + produces an internal binary instruction tree the first time the + function is called (within any one backend process). The instruction tree + fully translates the + PL/pgSQL statement structure, but individual + SQL expressions and SQL queries + used in the function are not translated immediately. + + + As each expression and SQL query is first used + in the function, the PL/pgSQL interpreter creates a + prepared execution plan (using the SPI manager's + SPI_prepare and + SPI_saveplan functions). Subsequent visits + to that expression or query re-use the prepared plan. Thus, a function + with conditional code that contains many statements for which execution + plans might be required, will only prepare and save those plans + that are really used during the lifetime of the database + connection. This can provide a considerable savings of parsing + activity. A disadvantage is that errors in a specific expression + or query may not be detected until that part of the function is + reached in execution. + + + Once PL/pgSQL has made a query plan for a particular + query in a function, it will re-use that plan for the life of the + database connection. This is usually a win for performance, but it + can cause some problems if you dynamically + alter your database schema. For example: + + +CREATE FUNCTION populate() RETURNS INTEGER AS ' +DECLARE + -- Declarations +BEGIN + PERFORM my_function(); +END; +' LANGUAGE 'plpgsql'; + + If you execute the above function, it will reference the OID for + my_function() in the query plan produced for + the PERFORM statement. Later, if you + drop and re-create my_function(), then + populate() will not be able to find + my_function() anymore. You would then have to + re-create populate(), or at least start a new + database session so that it will be compiled afresh. + + + + Because PL/pgSQL saves execution plans in this way, queries that appear + directly in a PL/pgSQL function must refer to the same tables and fields + on every execution; that is, you cannot use a parameter as the name of + a table or field in a query. To get around + this restriction, you can construct dynamic queries using the PL/pgSQL + EXECUTE statement --- at the price of constructing a new query plan + on every execution. + + + Except for input/output conversion and calculation functions + for user defined types, anything that can be defined in C language + functions can also be done with PL/pgSQL. It is possible to + create complex conditional computation functions and later use + them to define operators or use them in functional indexes. + + + Advantages of Using PL/pgSQL + + + + + Better performance (see ) + + + + + + SQL support (see ) + + + + + + Portability (see ) + + + + + + Better Performance + + + SQL is the language PostgreSQL (and + most other Relational Databases) use as query + language. It's portable and easy to learn. But every + SQL statement must be executed + individually by the database server. + + + + That means that your client application must send each + query to the database server, wait for it to process it, + receive the results, do some computation, then send + other queries to the server. All this incurs inter-process communication + and may also incur network + overhead if your client is on a different machine than + the database server. + + + + With PL/pgSQL you can group a block of computation and a + series of queries inside the + database server, thus having the power of a procedural + language and the ease of use of SQL, but saving lots of + time because you don't have the whole client/server + communication overhead. This can make for a + considerable performance increase. + + + + + SQL Support + + + PL/pgSQL adds the power of a procedural language to the + flexibility and ease of SQL. With + PL/pgSQL you can use all the data types, columns, operators + and functions of SQL. + + + + + Portability + + + Because PL/pgSQL functions run inside PostgreSQL, these + functions will run on any platform where PostgreSQL + runs. Thus you can reuse code and have less development costs. + + + + + + Developing in <application>PL/pgSQL</application> + + + Developing in PL/pgSQL is pretty straight forward, especially + if you have developed in other database procedural languages, + such as Oracle's PL/SQL. Two good ways of developing in + PL/pgSQL are: + + + + + Using a text editor and reloading the file with psql + + + + + + Using PostgreSQL's GUI Tool: PgAccess + + + + + + + One good way to develop in PL/pgSQL is to simply use the text + editor of your choice to create your functions, and in another + console, use psql (PostgreSQL's interactive monitor) to load + those functions. If you are doing it this way, it is a good + idea to write the function using CREATE OR REPLACE + FUNCTION. That way you can reload the file to update + the function definition. For example: + +CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS ' + .... +end; +' LANGUAGE 'plpgsql'; + + + + + While running psql, you can load or reload such a + function definition file with + + \i filename.sql + + and then immediately issue SQL commands to test the function. + + + + Another good way to develop in PL/pgSQL is using + PostgreSQL's GUI tool: PgAccess. It does some + nice things for you, like escaping single-quotes, and making + it easy to recreate and debug functions. + + + + + + Structure of <application>PL/pgSQL</application> + + + PL/pgSQL is a block + structured language. The complete text of a function + definition must be a block. A block is defined as: + + + <<label>> + DECLARE + declarations +BEGIN + statements +END; + + + + + Any statement in the statement section of a block + can be a sub-block. Sub-blocks can be used for + logical grouping or to localize variables to a small group + of statements. + + + + The variables declared in the declarations section preceding a + block are initialized to their default values every time the + block is entered, not only once per function call. For example: + +CREATE FUNCTION somefunc() RETURNS INTEGER AS ' +DECLARE + quantity INTEGER := 30; +BEGIN + RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 30 + quantity := 50; + -- + -- Create a sub-block + -- + DECLARE + quantity INTEGER := 80; + BEGIN + RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 80 + END; + + RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 50 + + RETURN quantity; +END; +' LANGUAGE 'plpgsql'; + + + + + It is important not to confuse the use of BEGIN/END for + grouping statements in PL/pgSQL with the database commands for + transaction control. PL/pgSQL's BEGIN/END are only for grouping; + they do not start or end a transaction. Functions and trigger procedures + are always executed within a transaction established by an outer query + --- they cannot start or commit transactions, since + PostgreSQL does not have nested transactions. + + + + Lexical Details + + + Each statement and declaration within a block is terminated + by a semicolon. + + + + All keywords and identifiers can be written in mixed upper- and + lower-case. Identifiers are implicitly converted to lower-case + unless double-quoted. + + + + There are two types of comments in PL/pgSQL. A double dash -- + starts a comment that extends to the end of the line. A /* + starts a block comment that extends to the next occurrence of */. + Block comments cannot be nested, but double dash comments can be + enclosed into a block comment and a double dash can hide + the block comment delimiters /* and */. + + + + + + Declarations + + + All variables, rows and records used in a block must be declared in the + declarations section of the block. + (The only exception is that the loop variable of a FOR loop iterating + over a range of integer values is automatically declared as an integer + variable.) + + + + PL/pgSQL variables can have any SQL data type, such as + INTEGER, VARCHAR and + CHAR. + + + + Here are some examples of variable declarations: + +user_id INTEGER; +quantity NUMERIC(5); +url VARCHAR; +myrow tablename%ROWTYPE; +myfield tablename.fieldname%TYPE; +arow RECORD; + + + + + The general syntax of a variable declaration is: + +name CONSTANT type NOT NULL { DEFAULT | := } expression ; + + + + + The DEFAULT clause, if given, specifies the initial value assigned + to the variable when the block is entered. If the DEFAULT clause + is not given then the variable is initialized to the + SQL NULL value. + + + + The CONSTANT option prevents the variable from being assigned to, + so that its value remains constant for the duration of the block. + If NOT NULL + is specified, an assignment of a NULL value results in a run-time + error. All variables declared as NOT NULL + must have a non-NULL default value specified. + + + + The default value is evaluated every time the block is entered. So, + for example, assigning 'now' to a variable of type + timestamp causes the variable to have the + time of the current function call, not when the function was + precompiled. + + + + Examples: + +quantity INTEGER DEFAULT 32; +url varchar := ''http://mysite.com''; +user_id CONSTANT INTEGER := 10; + + + + + Aliases for Function Parameters + + + +name ALIAS FOR $n; + + + + + Parameters passed to functions are named with the identifiers + $1, $2, + etc. Optionally, aliases can be declared for $n + parameter names for increased readability. Either the alias or the + numeric identifier can then be used to refer to the parameter value. + Some examples: + +CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS ' +DECLARE + subtotal ALIAS FOR $1; +BEGIN + return subtotal * 0.06; +END; +' LANGUAGE 'plpgsql'; + + +CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS ' +DECLARE + v_string ALIAS FOR $1; + index ALIAS FOR $2; +BEGIN + -- Some computations here +END; +' LANGUAGE 'plpgsql'; + + +CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS ' +DECLARE + in_t ALIAS FOR $1; +BEGIN + RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; +END; +' LANGUAGE 'plpgsql'; + + + + + + Row Types + + + +name tablename%ROWTYPE; + + + + + A variable of a composite type is called a row + variable (or row-type variable). Such a variable can hold a + whole row of a SELECT or FOR + query result, so long as that query's column set matches the declared + type of the variable. The individual fields of the row value are + accessed using the usual dot notation, for example + rowvar.field. + + + + Presently, a row variable can only be declared using the + %ROWTYPE notation; although one might expect a + bare table name to work as a type declaration, it won't be accepted + within PL/pgSQL functions. + + + + Parameters to a function can be + composite types (complete table rows). In that case, the + corresponding identifier $n will be a row variable, and fields can + be selected from it, for example $1.user_id. + + + + Only the user-defined attributes of a table row are accessible in a + row-type variable, not OID or other system attributes (because the + row could be from a view). The fields of the row type inherit the + table's field size or precision for data types such as + char(n). + +CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS ' +DECLARE + in_t ALIAS FOR $1; + use_t table2name%ROWTYPE; +BEGIN + SELECT * INTO use_t FROM table2name WHERE ... ; + RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7; +END; +' LANGUAGE 'plpgsql'; + + + + + + Records + + + +name RECORD; + + + + + Record variables are similar to row-type variables, but they have no + predefined structure. They take on the actual row structure of the + row they are assigned during a SELECT or FOR command. The substructure + of a record variable can change each time it is assigned to. + A consequence of this is that until a record variable is first assigned + to, it has no substructure, and any attempt to access a + field in it will draw a run-time error. + + + + Note that RECORD is not a true data type, only a placeholder. + Thus, for example, one cannot declare a function returning + RECORD. + + + + + Attributes + + + Using the %TYPE and %ROWTYPE + attributes, you can declare variables with the same + data type or structure as another database item (e.g: a + table field). + + + + + + variable%TYPE + + + + %TYPE provides the data type of a + variable or database column. You can use this to + declare variables that will hold database + values. For example, let's say you have a column + named user_id in your + users table. To declare a variable with + the same data type as users.user_id you write: + +user_id users.user_id%TYPE; + + + + + By using %TYPE you don't need to know + the data type of the structure you are referencing, + and most important, if the data type of the + referenced item changes in the future (e.g: you + change your table definition of user_id from INTEGER to + REAL), you may not need to change your function + definition. + + + + + + + table%ROWTYPE + + + + %ROWTYPE provides the composite data type corresponding + to a whole row of the specified table. + table must be an existing + table or view name of the database. + + + +DECLARE + users_rec users%ROWTYPE; + user_id users.user_id%TYPE; +BEGIN + user_id := users_rec.user_id; + ... + +CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS ' + DECLARE + key ALIAS FOR $1; + table_data cs_materialized_views%ROWTYPE; + BEGIN + SELECT INTO table_data * FROM cs_materialized_views + WHERE sort_key=key; + + IF NOT FOUND THEN + RETURN false; + END IF; + RETURN true; + END; +' LANGUAGE 'plpgsql'; + + + + + + + + RENAME + + + +RENAME oldname TO newname; + + + Using the RENAME declaration you can change the name of a variable, + record or row. This is primarily useful if NEW or OLD should be + referenced by another name inside a trigger procedure. See also ALIAS. + + + + Examples: + +RENAME id TO user_id; +RENAME this_var TO that_var; + + + + + + RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is + of low priority, since ALIAS covers most of the practical uses of + RENAME. + + + + + + + + Expressions + + + All expressions used in PL/pgSQL statements + are processed using the server's regular SQL executor. Expressions that + appear to contain + constants may in fact require run-time evaluation + (e.g. 'now' for the + timestamp type) so + it is impossible for the PL/pgSQL parser + to identify real constant values other than the NULL keyword. All + expressions are evaluated internally by executing a query + +SELECT expression + + using the SPI manager. In the expression, occurrences + of PL/pgSQL variable + identifiers are replaced by parameters and the actual values from + the variables are passed to the executor in the parameter array. + This allows the query plan for the SELECT to be prepared just once + and then re-used for subsequent evaluations. + + + + The evaluation done by the PostgreSQL + main parser has some side + effects on the interpretation of constant values. In detail there + is a difference between what these two functions do: + + +CREATE FUNCTION logfunc1 (TEXT) RETURNS TIMESTAMP AS ' + DECLARE + logtxt ALIAS FOR $1; + BEGIN + INSERT INTO logtable VALUES (logtxt, ''now''); + RETURN ''now''; + END; +' LANGUAGE 'plpgsql'; + + + and + + +CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS ' + DECLARE + logtxt ALIAS FOR $1; + curtime timestamp; + BEGIN + curtime := ''now''; + INSERT INTO logtable VALUES (logtxt, curtime); + RETURN curtime; + END; +' LANGUAGE 'plpgsql'; + + + In the case of logfunc1(), the + PostgreSQL main parser knows when + preparing the plan for the INSERT, that the string + 'now' should be interpreted as + timestamp because the target field of logtable + is of that type. Thus, it will make a constant from it at this + time and this constant value is then used in all invocations of + logfunc1() during the lifetime of the + backend. Needless to say that this isn't what the + programmer wanted. + + + + In the case of logfunc2(), the + PostgreSQL main parser does not know + what type 'now' should become and therefore + it returns a data value of type text containing the string + 'now'. During the ensuing assignment + to the local variable curtime, the + PL/pgSQL interpreter casts this + string to the timestamp type by calling the + text_out() and timestamp_in() + functions for the conversion. So, the computed time stamp is updated + on each execution as the programmer expects. + + + + The mutable nature of record variables presents a problem in this + connection. When fields of a record variable are used in expressions or + statements, the data types of the + fields must not change between calls of one and the same expression, + since the expression will be planned using the data type that is present + when the expression is first reached. + Keep this in mind when writing trigger procedures that handle events + for more than one table. (EXECUTE can be used to get around this + problem when necessary.) + + + + + Basic Statements + + + In this section and the following ones, we describe all the statement + types that are explicitly understood by + PL/pgSQL. + Anything not recognized as one of these statement types is presumed + to be an SQL query, and is sent to the main database engine to execute + (after substitution for any PL/pgSQL variables + used in the statement). Thus, + for example, SQL INSERT, UPDATE, and + DELETE commands may be considered to be statements of + PL/pgSQL. But they are not specifically + listed here. + + + + Assignment + + + An assignment of a value to a variable or row/record field is + written as: + +identifier := expression; + + As explained above, the expression in such a statement is evaluated + by means of an SQL SELECT command sent to the main + database engine. The expression must yield a single value. + + + + If the expression's result data type doesn't match the variable's + data type, or the variable has a specific size/precision + (like char(20)), the result value will be implicitly + converted by the PL/pgSQL interpreter using + the result type's output-function and + the variable type's input-function. Note that this could potentially + result in run-time errors generated by the input function, if the + string form of the result value is not acceptable to the input function. + + + + Examples: + +user_id := 20; +tax := subtotal * 0.06; + + + + + + SELECT INTO + + + The result of a SELECT command yielding multiple columns (but + only one row) can be assigned to a record variable, row-type + variable, or list of scalar variables. This is done by: + + +SELECT INTO target expressions FROM ...; + + + where target can be a record variable, a row + variable, or a comma-separated list of simple variables and + record/row fields. Note that this is quite different from + PostgreSQL's normal interpretation of SELECT INTO, which is that the + INTO target is a newly created table. (If you want to create a + table from a SELECT result inside a PL/pgSQL function, use the + syntax CREATE TABLE ... AS SELECT.) + + + + If a row or a variable list is used as target, the selected values + must exactly match the structure of the target(s), or a run-time error + occurs. When a record variable is the target, it automatically + configures itself to the row type of the query result columns. + + + + Except for the INTO clause, the SELECT statement is the same as a normal + SQL SELECT query and can use the full power of SELECT. + + + + If the SELECT query returns zero rows, NULLs are assigned to the + target(s). If the SELECT query returns multiple rows, the first + row is assigned to the target(s) and the rest are discarded. + (Note that the first row is not well-defined unless you've + used ORDER BY.) + + + + At present, the INTO clause can appear almost anywhere in the SELECT + query, but it is recommended to place it immediately after the SELECT + keyword as depicted above. Future versions of + PL/pgSQL may be less forgiving about + placement of the INTO clause. + + + + There is a special variable named FOUND of type + boolean that can be used immediately after a SELECT + INTO to check if an assignment had success (that is, at least one + row was returned by the SELECT). For example, + + +SELECT INTO myrec * FROM EMP WHERE empname = myname; +IF NOT FOUND THEN + RAISE EXCEPTION ''employee % not found'', myname; +END IF; + + + Alternatively, you can use the IS NULL (or ISNULL) conditional to + test for NULLity of a RECORD/ROW result. Note that there is no + way to tell whether any additional rows might have been discarded. + + + + +DECLARE + users_rec RECORD; + full_name varchar; +BEGIN + SELECT INTO users_rec * FROM users WHERE user_id=3; + + IF users_rec.homepage IS NULL THEN + -- user entered no homepage, return "http://" + + RETURN ''http://''; + END IF; +END; + + + + + + Executing an expression or query with no result + + + Sometimes one wishes to evaluate an expression or query but discard + the result (typically because one is calling a function that has + useful side-effects but no useful result value). To do this in + PL/pgSQL, use the PERFORM statement: + + +PERFORM query; + + + This executes a SELECT + query and discards the + result. PL/pgSQL variables are substituted + in the query as usual. Also, the special variable FOUND is set to + true if the query produced at least one row, or false if it produced + no rows. + + + + + One might expect that SELECT with no INTO clause would accomplish + this result, but at present the only accepted way to do it is PERFORM. + + + + + An example: + +PERFORM create_mv(''cs_session_page_requests_mv'', my_query); + + + + + + Executing dynamic queries + + + Oftentimes you will want to generate dynamic queries inside + your PL/pgSQL functions, that is, + queries that will involve different tables or different data types + each time they are executed. PL/pgSQL's + normal attempts to cache plans for queries will not work in such + scenarios. To handle this sort of problem, the EXECUTE statement + is provided: + + +EXECUTE query-string; + + + where query-string is an expression + yielding a string (of type + text) containing the query + to be executed. This string is fed literally to the SQL engine. + + + + Note in particular that no substitution of PL/pgSQL + variables is done on the query string. The values of variables must + be inserted in the query string as it is constructed. + + + + When working with dynamic queries you will have to face + escaping of single quotes in PL/pgSQL. Please refer to the + table in + for a detailed explanation that will save you some effort. + + + + Unlike all other queries in PL/pgSQL, a + query run by an EXECUTE statement is + not prepared and saved just once during the life of the server. + Instead, the query is prepared each + time the statement is run. The + query-string can be dynamically + created within the procedure to perform actions on variable + tables and fields. + + + + The results from SELECT queries are discarded by EXECUTE, and + SELECT INTO is not currently supported within EXECUTE. So, the + only way to extract a result from a dynamically-created SELECT is + to use the FOR-IN-EXECUTE form described later. + + + + An example: + + +EXECUTE ''UPDATE tbl SET '' + || quote_ident(fieldname) + || '' = '' + || quote_literal(newvalue) + || '' WHERE ...''; + + + + + + This example shows use of the functions + quote_ident(TEXT) and + quote_literal(TEXT). + Variables containing field and table identifiers should be + passed to function quote_ident(). + Variables containing literal elements of the dynamic query + string should be passed to + quote_literal(). Both take the + appropriate steps to return the input text enclosed in single + or double quotes and with any embedded special characters + properly escaped. + + + + Here is a much larger example of a dynamic query and EXECUTE: + +CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' +DECLARE + referrer_keys RECORD; -- Declare a generic record to be used in a FOR + a_output varchar(4000); +BEGIN + a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) + RETURNS VARCHAR AS '''' + DECLARE + v_host ALIAS FOR $1; + v_domain ALIAS FOR $2; + v_url ALIAS FOR $3; + BEGIN ''; + + -- + -- Notice how we scan through the results of a query in a FOR loop + -- using the FOR <record> construct. + -- + + FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP + a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' + || referrer_keys.key_string || '''''''''' THEN RETURN '''''' + || referrer_keys.referrer_type || ''''''; END IF;''; + END LOOP; + + a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';''; + + -- This works because we are not substituting any variables + -- Otherwise it would fail. Look at PERFORM for another way to run functions + + EXECUTE a_output; +END; +' LANGUAGE 'plpgsql'; + + + + + + Obtaining result status + + + +GET DIAGNOSTICS variable = item , ... ; + + + This command allows retrieval of system status indicators. Each + item is a keyword identifying a state + value to be assigned to the specified variable (which should be + of the right data type to receive it). The currently available + status items are ROW_COUNT, the number of rows + processed by the last SQL query sent down to + the SQL engine; and RESULT_OID, + the OID of the last row inserted by the most recent + SQL query. Note that RESULT_OID + is only useful after an INSERT query. + + + + + GET DIAGNOSTICS var_integer = ROW_COUNT; + + + + + + + + Control Structures + + + 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. + + + + Returning from a function + + + +RETURN expression; + + + The function terminates and the value of + expression will be returned to the + upper executor. + The expression's result will be automatically cast into the + function's return type as described for assignments. + + + + The return value of a function cannot be left undefined. If control + reaches the end of the top-level block of + the function without hitting a RETURN statement, a run-time error + will occur. + + + + + Conditionals + + + IF statements let you execute commands based on + certain conditions. + PL/pgSQL has four forms of IF: IF-THEN, IF-THEN-ELSE, + IF-THEN-ELSE IF, and IF-THEN-ELSIF-THEN-ELSE. + + + + IF-THEN + + + +IF boolean-expression THEN + statements +END IF; + + + IF-THEN statements are the simplest form of IF. The + statements between THEN and END IF will be executed if + the condition is true. Otherwise, they are skipped. + + +IF v_user_id <> 0 THEN + UPDATE users SET email = v_email WHERE user_id = v_user_id; +END IF; + + + + + + IF-THEN-ELSE + + + +IF boolean-expression THEN + statements +ELSE + statements +END IF; + + + IF-THEN-ELSE statements add to IF-THEN by letting you + specify an alternative set of statements that should be executed if + the condition evaluates to FALSE. + + +IF parentid IS NULL or parentid = '''' +THEN + return fullname; +ELSE + return hp_true_filename(parentid) || ''/'' || fullname; +END IF; + + +IF v_count > 0 THEN + INSERT INTO users_count(count) VALUES(v_count); + return ''t''; +ELSE + return ''f''; +END IF; + + + + + + IF-THEN-ELSE IF + + + IF statements can be nested, as in the following example: + +IF demo_row.sex = ''m'' THEN + pretty_sex := ''man''; +ELSE + IF demo_row.sex = ''f'' THEN + pretty_sex := ''woman''; + END IF; +END IF; + + + + + When you use this form, you are actually + nesting an IF statement inside the ELSE part of an outer IF + statement. Thus you need one END IF statement for each + nested IF and one for the parent IF-ELSE. + This is workable but grows tedious when there are many + alternatives to be checked. + + + + + IF-THEN-ELSIF-ELSE + + + +IF boolean-expression THEN + statements + ELSIF boolean-expression THEN + statements + ELSIF boolean-expression THEN + statements + ... + + + ELSE + statements +END IF; + + + IF-THEN-ELSIF-ELSE provides a more convenient method of checking + many alternatives in one statement. Formally it is equivalent + to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF + is needed. + + + + Here is an example: + + +IF number = 0 THEN + result := ''zero''; +ELSIF number > 0 THEN + result := ''positive''; +ELSIF number < 0 THEN + result := ''negative''; +ELSE + -- hmm, the only other possibility is that number IS NULL + result := ''NULL''; +END IF; + + + + + The final ELSE section is optional. + + + + + + + Simple Loops + + + With the LOOP, EXIT, WHILE and FOR statements, you can arrange + for your PL/pgSQL function to repeat + a series of commands. + + + + LOOP + + + +<<label>> +LOOP + statements +END LOOP; + + + LOOP defines an unconditional loop that is repeated indefinitely + until terminated by an EXIT or RETURN statement. + The optional label can be used by + EXIT statements in nested loops to specify which level of + nesting should be terminated. + + + + + EXIT + + + +EXIT label WHEN expression ; + + + If no label is given, + the innermost loop is terminated and the + statement following END LOOP is executed next. + If label is 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 corresponding + END. + + + + If WHEN is present, loop exit occurs only if the specified condition + is true, otherwise control passes to the statement after EXIT. + + + + Examples: + +LOOP + -- some computations + IF count > 0 THEN + EXIT; -- exit loop + END IF; +END LOOP; + +LOOP + -- some computations + EXIT WHEN count > 0; +END LOOP; + +BEGIN + -- some computations + IF stocks > 100000 THEN + EXIT; -- illegal. Can't use EXIT outside of a LOOP + END IF; +END; + + + + + + WHILE + + + +<<label>> +WHILE expression LOOP + statements +END LOOP; + + + The WHILE statement repeats a + sequence of statements so long as the condition expression + evaluates to true. The condition is checked just before + each entry to the loop body. + + + + For example: + +WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP + -- some computations here +END LOOP; + +WHILE NOT boolean_expression LOOP + -- some computations here +END LOOP; + + + + + + FOR (integer for-loop) + + + +<<label>> +FOR name IN REVERSE expression .. expression LOOP + statements +END LOOP; + + + This form of FOR creates a loop that iterates over a range of integer + values. The variable + name is automatically defined as type + integer and exists only inside the loop. The two expressions giving + the lower and upper bound of the range are evaluated once when entering + the loop. The iteration step is normally 1, but is -1 when REVERSE is + specified. + + + + Some examples of integer FOR loops: + +FOR i IN 1..10 LOOP + -- some expressions here + + RAISE NOTICE ''i is %'',i; +END LOOP; + +FOR i IN REVERSE 10..1 LOOP + -- some expressions here +END LOOP; + + + + + + + Looping Through Query Results + + + Using a different type of FOR loop, you can iterate through + the results of a query and manipulate that data + accordingly. The syntax is: + +<<label>> +FOR record | row IN select_query LOOP + statements +END LOOP; + + The record or row variable is successively assigned all the rows + resulting from the SELECT query and the loop body is executed + for each row. Here is an example: + + + + +CREATE FUNCTION cs_refresh_mviews () RETURNS INTEGER AS ' +DECLARE + mviews RECORD; +BEGIN + PERFORM cs_log(''Refreshing materialized views...''); + + FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP + + -- Now "mviews" has one record from cs_materialized_views + + PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...''); + EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name); + EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query; + END LOOP; + + PERFORM cs_log(''Done refreshing materialized views.''); + RETURN 1; +end; +' LANGUAGE 'plpgsql'; + + + If the loop is terminated by an EXIT statement, the last + assigned row value is still accessible after the loop. + + + + The FOR-IN-EXECUTE statement is another way to iterate over + records: + +<<label>> +FOR record | row IN EXECUTE text_expression LOOP + statements +END LOOP; + + This is like the previous form, except that the source SELECT + statement is specified as a string expression, which is evaluated + and re-planned on each entry to the FOR loop. This allows the + programmer to choose the speed of a pre-planned query or the + flexibility of a dynamic query, just as with a plain EXECUTE + statement. + + + + + The PL/pgSQL parser presently distinguishes the + two kinds of FOR loops (integer or record-returning) by checking + whether the target variable mentioned just after FOR has been + declared as a record/row variable. If not, it's presumed to be + an integer FOR loop. This can cause rather nonintuitive error + messages when the true problem is, say, that one has + misspelled the FOR variable name. + + + + + + + Cursors + + + 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. One reason for doing this is + to avoid memory overrun when the result contains a large number of + rows. (However, PL/pgSQL users don't normally need + to worry about that, since FOR loops automatically use a cursor + internally to avoid memory problems.) A more interesting usage is to + return a reference to a cursor that it has created, allowing the + caller to read the rows. This provides a way to return row sets + from functions. + + + + Declaring Cursor Variables + + + All access to cursors in PL/pgSQL goes through + cursor variables, which are always of the special data type + refcursor. One way to create a cursor variable + is just to declare it as a variable of type refcursor. + Another way is to use the cursor declaration syntax, + which in general is: + +name CURSOR ( arguments ) FOR select_query ; + + (FOR may be replaced by IS for Oracle + compatibility.) arguments, if any, + are a comma-separated list of name + datatype pairs that define names to + be replaced by parameter values in the given query. The actual + values to substitute for these names will be specified later, + when the cursor is opened. + + + Some examples: + +DECLARE + curs1 refcursor; + curs2 CURSOR FOR SELECT * from tenk1; + curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key; + + All three of these variables have the data type refcursor, + but the first may 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. (key will be + replaced by an integer parameter value when the cursor is opened.) + The variable curs1 + is said to be unbound since it is not bound to + any particular query. + + + + + Opening Cursors + + + Before a cursor can be used to retrieve rows, it must be + opened. (This is the equivalent action to the SQL + command DECLARE CURSOR.) PL/pgSQL has + four forms of the OPEN statement, two of which use unbound cursor + variables and the other two use bound cursor variables. + + + + OPEN FOR SELECT + + + +OPEN unbound-cursor FOR SELECT ...; + + + The cursor variable is opened and given the specified query + to execute. The cursor cannot be open already, and it must + have been declared as an unbound cursor (that is, as a simple + refcursor variable). The SELECT query is treated + in the same way as other SELECTs in PL/pgSQL: + PL/pgSQL variable names are substituted, + and the query plan is cached for possible re-use. + + +OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; + + + + + + OPEN FOR EXECUTE + + + +OPEN unbound-cursor FOR EXECUTE query-string; + + + The cursor variable is opened and given the specified query + to execute. The cursor cannot be open already, and it must + have been declared as an unbound cursor (that is, as a simple + refcursor variable). The query is specified as a + string expression in the same way as in the EXECUTE command. + As usual, this gives flexibility so the query can vary + from one run to the next. + + +OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1); + + + + + + OPENing a bound cursor + + + +OPEN bound-cursor ( argument_values ) ; + + + This form of OPEN is used to open a cursor variable whose query + was bound to it when it was declared. + The cursor cannot be open already. 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. + The query plan for a bound cursor is always considered + cacheable --- there is no equivalent of EXECUTE in this case. + + +OPEN curs2; +OPEN curs3(42); + + + + + + + Using Cursors + + + Once a cursor has been opened, it can be manipulated with the + statements described here. + + + + These manipulations need not occur in the same function that + opened the cursor to begin with. You can return a refcursor + value out of a function and let the caller operate on the cursor. + (Internally, a refcursor value is simply the string name + of a Portal containing the active query for the cursor. This name + can be passed around, assigned to other refcursor variables, + and so on, without disturbing the Portal.) + + + + All Portals are implicitly closed at transaction end. Therefore + a refcursor value is useful to reference an open cursor + only until the end of the transaction. + + + + FETCH + + + +FETCH cursor INTO target; + + + FETCH retrieves the next row from the cursor into a target, + which may be a row variable, a record variable, or a comma-separated + list of simple variables, just like SELECT INTO. As with + SELECT INTO, the special variable FOUND may be checked to see + whether a row was obtained or not. + + +FETCH curs1 INTO rowvar; +FETCH curs2 INTO foo,bar,baz; + + + + + + CLOSE + + + +CLOSE cursor; + + + CLOSE closes 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 again. + + +CLOSE curs1; + + + + + + Returning Cursors + + + + PL/pgSQL functions can return cursors to the + caller. This is used to return multiple rows or columns from the + function. The function opens the cursor and returns the cursor + name to the caller. The caller can then FETCH rows from the + cursor. The cursor can be CLOSEd by the caller, or it will be + closed automatically when the transaction closes. + + + + + The cursor name returned by the function can be specified by the + caller or automatically generated. The following example shows + how a cursor name can be supplied by the caller: + + +CREATE TABLE test (col text); +INSERT INTO test VALUES ('123'); + +CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' +BEGIN + OPEN $1 FOR SELECT col FROM test; + RETURN $1; +END; +' LANGUAGE 'plpgsql'; + +BEGIN; +SELECT reffunc('funccursor'); +FETCH ALL IN funccursor; +COMMIT; + + + + + The following example uses automatic cursor name generation: + + +CREATE FUNCTION reffunc2() RETURNS refcursor AS ' +DECLARE + ref refcursor; +BEGIN + OPEN ref FOR SELECT col FROM test; + RETURN ref; +END; +' LANGUAGE 'plpgsql'; + +BEGIN; +SELECT reffunc2(); + + reffunc2 + -------------------- + <unnamed cursor 1> + (1 row) + +FETCH ALL IN "<unnamed cursor 1>"; +COMMIT; + + + + + + + + Errors and Messages + + + Use the RAISE statement to report messages and raise errors. + + +RAISE level 'format' , variable ...; + + + Possible levels are DEBUG (write the message into the postmaster log), + NOTICE (write the message into the postmaster log and forward it to + the client application) and EXCEPTION (raise an error, + aborting the transaction). + + + + Inside the format string, % is replaced by the next + optional argument's external representation. + Write %% to emit a literal %. + Note that the optional arguments must presently + be simple variables, not expressions, and the format must be a simple + string literal. + + + + + + Examples: + +RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id; + + In this example, the value of v_job_id will replace the % in the + string. + + + + +RAISE EXCEPTION ''Inexistent ID --> %'',user_id; + + This will abort the transaction with the given error message. + + + + Exceptions + + + PostgreSQL does not have a very smart + exception handling model. Whenever the parser, planner/optimizer + or executor decide that a statement cannot be processed any longer, + the whole transaction gets aborted and the system jumps back + into the main loop to get the next query from the client application. + + + + It is possible to hook into the error mechanism to notice that this + happens. But currently it is impossible to tell what really + caused the abort (input/output conversion error, floating-point + error, parse error). And it is possible that the database backend + is in an inconsistent state at this point so returning to the upper + executor or issuing more commands might corrupt the whole database. + + + + Thus, the only thing PL/pgSQL currently does when it encounters + an abort during execution of a function or trigger + procedure is to write some additional NOTICE level log messages + telling in which function and where (line number and type of + statement) this happened. The error always stops execution of + the function. + + + + + + Trigger Procedures + + + PL/pgSQL can be used to define trigger + procedures. A trigger procedure is created with the CREATE + FUNCTION command as a function with no arguments and a return + type of OPAQUE. 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: + + + + NEW + + + Data type RECORD; variable holding the new database row for INSERT/UPDATE + operations in ROW level triggers. + + + + + + OLD + + + Data type RECORD; variable holding the old database row for UPDATE/DELETE + operations in ROW level triggers. + + + + + + TG_NAME + + + Data type name; variable that contains the name of the trigger actually + fired. + + + + + + TG_WHEN + + + Data type text; a string of either + BEFORE or AFTER + depending on the trigger's definition. + + + + + + TG_LEVEL + + + Data type text; a string of either + ROW or STATEMENT depending on the + trigger's definition. + + + + + + TG_OP + + + Data type text; a string of + INSERT, UPDATE + or DELETE telling + for which operation the trigger is fired. + + + + + + TG_RELID + + + Data type oid; the object ID of the table that caused the + trigger invocation. + + + + + + TG_RELNAME + + + Data type name; the name of the table that caused the trigger + invocation. + + + + + + TG_NARGS + + + Data type integer; the number of arguments given to the trigger + procedure in the CREATE TRIGGER statement. + + + + + + TG_ARGV[] + + + Data type array of text; the arguments from + the CREATE TRIGGER statement. + The index counts from 0 and can be given as an expression. Invalid + indices (< 0 or >= tg_nargs) result in a NULL value. + + + + + + + + A trigger function must return either NULL or a record/row value + having exactly the structure of the table the trigger was fired for. + Triggers fired BEFORE may return NULL to signal the trigger manager + to skip the rest of the operation for this row (ie, subsequent triggers + are not fired, and the INSERT/UPDATE/DELETE does not occur for this + row). If a non-NULL value is returned then the operation proceeds with + that row value. Note that returning a row value different from the + original value of NEW alters the row that will be inserted or updated. + It is possible to replace single values directly + in NEW and return that, or to build a complete new record/row to + return. + + + + The return value of a trigger fired AFTER is ignored; it may as well + always return a NULL value. But an AFTER trigger can still abort the + operation by raising an error. + + + + A <application>PL/pgSQL</application> Trigger Procedure Example + + + This example trigger ensures that any time a row is inserted or updated + in the table, the current user name and time are stamped into the + row. And it ensures that an employee's name is given and that the + salary is a positive value. + + +CREATE TABLE emp ( + empname text, + salary integer, + last_date timestamp, + last_user text +); + +CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' + BEGIN + -- Check that empname and salary are given + IF NEW.empname ISNULL THEN + RAISE EXCEPTION ''empname cannot be NULL value''; + END IF; + IF NEW.salary ISNULL THEN + RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; + END IF; + + -- Who works for us when she must pay for? + IF NEW.salary < 0 THEN + RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; + END IF; + + -- Remember who changed the payroll when + NEW.last_date := ''now''; + NEW.last_user := current_user; + RETURN NEW; + END; +' LANGUAGE 'plpgsql'; + +CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp + FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); + + + + + + + + + Examples + + + Here are only a few functions to demonstrate how easy it is to + write PL/pgSQL + functions. For more complex examples the programmer + might look at the regression test for PL/pgSQL. + + + + One painful detail in writing functions in PL/pgSQL is the handling + of single quotes. The function's source text in CREATE FUNCTION must + be a literal string. Single quotes inside of literal strings must be + either doubled or quoted with a backslash. We are still looking for + an elegant alternative. In the meantime, doubling the single quotes + as in the examples below should be used. Any solution for this + in future versions of PostgreSQL will be + forward compatible. + + + + For a detailed explanation and examples of how to escape single + quotes in different situations, please see . + + + + A Simple <application>PL/pgSQL</application> Function to Increment an Integer + + + The following two PL/pgSQL functions are identical to their + counterparts from the C language function discussion. This + function receives an integer and increments it by + one, returning the incremented value. + + + +CREATE FUNCTION add_one (integer) RETURNS INTEGER AS ' + BEGIN + RETURN $1 + 1; + END; +' LANGUAGE 'plpgsql'; + + + + + A Simple <application>PL/pgSQL</application> Function to Concatenate Text + + + This function receives two text parameters and + returns the result of concatenating them. + + + +CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS ' + BEGIN + RETURN $1 || $2; + END; +' LANGUAGE 'plpgsql'; + + + + + A <application>PL/pgSQL</application> Function on Composite Type + + + In this example, we take EMP (a table) and an + integer as arguments to our function, which returns + a boolean. If the salary field of the EMP table is + NULL, we return f. Otherwise we compare with + that field with the integer passed to the function + and return the boolean result of the comparison (t + or f). This is the PL/pgSQL equivalent to the example from the C + functions. + + + +CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS ' + DECLARE + emprec ALIAS FOR $1; + sallim ALIAS FOR $2; + BEGIN + IF emprec.salary ISNULL THEN + RETURN ''f''; + END IF; + RETURN emprec.salary > sallim; + END; +' LANGUAGE 'plpgsql'; + + + + + + + + + + + February 2001 + + + Roberto + Mello + +
+ rmello@fslc.usu.edu +
+
+
+ +
+ + Porting from Oracle PL/SQL + + + Oracle + + + + PL/SQL + + + + Author + + Roberto Mello (rmello@fslc.usu.edu) + + + + + This section explains differences between Oracle's PL/SQL and + PostgreSQL's PL/pgSQL languages in the hopes of helping developers + port applications from Oracle to PostgreSQL. Most of the code here + is from the ArsDigita + Clickstream + module that I ported to PostgreSQL when I took an + internship with OpenForce + Inc. in the Summer of 2000. + + + + PL/pgSQL is similar to PL/SQL in many aspects. It is a block + structured, imperative language (all variables have to be + declared). PL/SQL has many more features than its PostgreSQL + counterpart, but PL/pgSQL allows for a great deal of functionality + and it is being improved constantly. + + + + Main Differences + + + Some things you should keep in mind when porting from Oracle to PostgreSQL: + + + + + No default parameters in PostgreSQL. + + + + + + You can overload functions in PostgreSQL. This is often used to work + around the lack of default parameters. + + + + + + Assignments, loops and conditionals are similar. + + + + + + No need for cursors in PostgreSQL, just put the query in the FOR + statement (see example below) + + + + + + In PostgreSQL you need to escape single + quotes. See . + + + + + + + Quote Me on That: Escaping Single Quotes + + + In PostgreSQL you need to escape single quotes inside your + function definition. This can lead to quite amusing code at + times, especially if you are creating a function that generates + other function(s), as in + . + One thing to keep in mind + when escaping lots of single quotes is that, except for the + beginning/ending quotes, all the others will come in even + quantity. + + + + gives the scoop. (You'll + love this little chart.) + + + + Single Quotes Escaping Chart + + + + + No. of Quotes + Usage + Example + Result + + + + + + 1 + To begin/terminate function bodies + +CREATE FUNCTION foo() RETURNS INTEGER AS '...' +LANGUAGE 'plpgsql'; + + as is + + + + 2 + In assignments, SELECTs, to delimit strings, etc. + +a_output := ''Blah''; +SELECT * FROM users WHERE f_name=''foobar''; + + SELECT * FROM users WHERE f_name='foobar'; + + + + 4 + + When you need two single quotes in your resulting string + without terminating that string. + + +a_output := a_output || '' AND name + LIKE ''''foobar'''' AND ...'' + + AND name LIKE 'foobar' AND ... + + + + 6 + + When you want double quotes in your resulting string + and terminate that string. + + +a_output := a_output || '' AND name + LIKE ''''foobar'''''' + + + AND name LIKE 'foobar' + + + + + 10 + + When you want two single quotes in the resulting string + (which accounts for 8 quotes) and + terminate that string (2 more). You will probably only need + that if you were using a function to generate other functions + (like in ). + + +a_output := a_output || '' if v_'' || + referrer_keys.kind || '' like '''''''''' + || referrer_keys.key_string || '''''''''' + then return '''''' || referrer_keys.referrer_type + || ''''''; end if;''; + + + if v_<...> like ''<...>'' then return ''<...>''; end if; + + + + +
+
+
+ + + + Porting Functions + + + + + A Simple Function + + + + Here is an Oracle function: + +CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN 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 to PL/pgSQL: + + + + + PostgreSQL does not have named + parameters. You have to explicitly alias them inside your + function. + + + + + + Oracle can have IN, OUT, + and INOUT parameters passed to functions. + The INOUT, for example, means that the + parameter will receive a value and return another. PostgreSQL + only has IN parameters and functions can return + only a single value. + + + + + + The RETURN key word in the function + prototype (not the function body) becomes + RETURNS in PostgreSQL. + + + + + + On PostgreSQL functions are created using single quotes as + delimiters, so you have to escape single quotes inside your + functions (which can be quite annoying at times; see ). + + + + + + The /show errors command does not exist in + PostgreSQL. + + + + + + + So let's see how this function would look when ported to + PostgreSQL: + + +CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR) +RETURNS VARCHAR AS ' +DECLARE + v_name ALIAS FOR $1; + v_version ALIAS FOR $2; +BEGIN + IF v_version IS NULL THEN + return v_name; + END IF; + RETURN v_name || ''/'' || v_version; +END; +' LANGUAGE 'plpgsql'; + + + + + + + A Function that Creates Another Function + + + + The following procedure grabs rows from a + SELECT statement and builds a large function + with the results in IF statements, for the + sake of efficiency. Notice particularly the differences in + cursors, FOR loops, and the need to escape + single quotes in PostgreSQL. + + +CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS + CURSOR referrer_keys IS + SELECT * FROM cs_referrer_keys + ORDER BY try_order; + + a_output VARCHAR(4000); +BEGIN + a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR, +v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; + + FOR referrer_key IN referrer_keys LOOP + a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' || +referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type || +'''; END IF;'; + END LOOP; + + a_output := a_output || ' RETURN NULL; END;'; + EXECUTE IMMEDIATE a_output; +END; +/ +show errors + + + + + Here is how this function would end up in PostgreSQL: + + +CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' +DECLARE + referrer_keys RECORD; -- Declare a generic record to be used in a FOR + a_output varchar(4000); +BEGIN + a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR) + RETURNS VARCHAR AS '''' + DECLARE + v_host ALIAS FOR $1; + v_domain ALIAS FOR $2; + v_url ALIAS FOR $3; + BEGIN ''; + + -- + -- Notice how we scan through the results of a query in a FOR loop + -- using the FOR <record> construct. + -- + + FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP + a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' + || referrer_keys.key_string || '''''''''' THEN RETURN '''''' + || referrer_keys.referrer_type || ''''''; END IF;''; + END LOOP; + + a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';''; + + -- This works because we are not substituting any variables + -- Otherwise it would fail. Look at PERFORM for another way to run functions + + EXECUTE a_output; +END; +' LANGUAGE 'plpgsql'; + + + + + + + A Procedure with a lot of String Manipulation and OUT Parameters + + + + The following Oracle PL/SQL procedure is used to parse a URL and + return several elements (host, path and query). It is an + procedure because in PL/pgSQL functions only one value can be returned + (see ). In + PostgreSQL, one way to work around this is to split the procedure + in three different functions: one to return the host, another for + the path and another for the query. + + + +CREATE OR REPLACE PROCEDURE cs_parse_url( + v_url IN VARCHAR, + v_host OUT VARCHAR, -- This will be passed back + v_path OUT VARCHAR, -- This one too + v_query OUT VARCHAR) -- And this one +is + a_pos1 INTEGER; + a_pos2 INTEGER; +begin + v_host := NULL; + v_path := NULL; + v_query := NULL; + a_pos1 := instr(v_url, '//'); -- PostgreSQL doesn't have an instr function + + IF a_pos1 = 0 THEN + RETURN; + END IF; + a_pos2 := instr(v_url, '/', a_pos1 + 2); + IF a_pos2 = 0 THEN + v_host := substr(v_url, a_pos1 + 2); + v_path := '/'; + RETURN; + END IF; + + v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); + a_pos1 := instr(v_url, '?', a_pos2 + 1); + + IF a_pos1 = 0 THEN + v_path := substr(v_url, a_pos2); + RETURN; + END IF; + + v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); + v_query := substr(v_url, a_pos1 + 1); +END; +/ +show errors; + + + + Here is how this procedure could be translated for PostgreSQL: + + +CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS ' +DECLARE + v_url ALIAS FOR $1; + v_host VARCHAR; + v_path VARCHAR; + a_pos1 INTEGER; + a_pos2 INTEGER; + a_pos3 INTEGER; +BEGIN + v_host := NULL; + a_pos1 := instr(v_url,''//''); + + IF a_pos1 = 0 THEN + RETURN ''''; -- Return a blank + END IF; + + a_pos2 := instr(v_url,''/'',a_pos1 + 2); + IF a_pos2 = 0 THEN + v_host := substr(v_url, a_pos1 + 2); + v_path := ''/''; + RETURN v_host; + END IF; + + v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); + RETURN v_host; +END; +' LANGUAGE 'plpgsql'; + + + + + + + PostgreSQL does not have an instr function, + so you can work around it using a combination of other functions. + I got tired of doing this and created my own + instr functions that behave exactly like + Oracle's (it makes life easier). See the for the code. + + + + + + + Procedures + + + + Oracle procedures give a little more flexibility to the developer + because nothing needs to be explicitly returned, but it can be + through the use of INOUT or OUT parameters. + + + + An example: + + +CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS + a_running_job_count INTEGER; + PRAGMA AUTONOMOUS_TRANSACTION; +BEGIN + LOCK TABLE cs_jobs IN EXCLUSIVE MODE; + + SELECT count(*) INTO a_running_job_count + FROM cs_jobs + WHERE end_stamp IS NULL; + + IF a_running_job_count > 0 THEN + COMMIT; -- free lock + raise_application_error(-20000, 'Unable to create a new job: a job is currently running.'); + END IF; + + DELETE FROM cs_active_job; + INSERT INTO cs_active_job(job_id) VALUES (v_job_id); + + BEGIN + INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate); + EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists + END; + COMMIT; +END; +/ +show errors + + + + + Procedures like this can be easily converted into PostgreSQL + functions returning an INTEGER. This procedure in + particular is interesting because it can teach us some things: + + + + + There is no pragma statement in PostgreSQL. + + + + + + If you do a LOCK TABLE in PL/pgSQL, the lock + will not be released until the calling transaction is finished. + + + + + + You also cannot have transactions in PL/pgSQL procedures. The + entire function (and other functions called from therein) is + executed in a transaction and PostgreSQL rolls back the results if + something goes wrong. Therefore only one + BEGIN statement is allowed. + + + + + + The exception when would have to be replaced by an + IF statement. + + + + + + + So let's see one of the ways we could port this procedure to PL/pgSQL: + + +CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS ' +DECLARE + v_job_id ALIAS FOR $1; + a_running_job_count INTEGER; + a_num INTEGER; + -- PRAGMA AUTONOMOUS_TRANSACTION; +BEGIN + LOCK TABLE cs_jobs IN EXCLUSIVE MODE; + SELECT count(*) INTO a_running_job_count + FROM cs_jobs + WHERE end_stamp IS NULL; + + IF a_running_job_count > 0 + THEN + -- COMMIT; -- free lock + RAISE EXCEPTION ''Unable to create a new job: a job is currently running.''; + END IF; + + DELETE FROM cs_active_job; + INSERT INTO cs_active_job(job_id) VALUES (v_job_id); + + SELECT count(*) into a_num + FROM cs_jobs + WHERE job_id=v_job_id; + IF NOT FOUND THEN -- If nothing was returned in the last query + -- This job is not in the table so lets insert it. + INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, sysdate()); + RETURN 1; + ELSE + RAISE NOTICE ''Job already running.''; + END IF; + + RETURN 0; +END; +' LANGUAGE 'plpgsql'; + + + + + + Notice how you can raise notices (or errors) in PL/pgSQL. + + + + + + + + + Packages + + + + + I haven't done much with packages myself, so if there are + mistakes here, please let me know. + + + + + Packages are a way Oracle gives you to encapsulate PL/SQL + statements and functions into one entity, like Java classes, where + you define methods and objects. You can access these + objects/methods with a . + (dot). Here is an example of an Oracle package from ACS 4 (the + ArsDigita Community + System): + + +CREATE OR REPLACE PACKAGE BODY acs +AS + FUNCTION add_user ( + user_id IN users.user_id%TYPE DEFAULT NULL, + object_type IN acs_objects.object_type%TYPE DEFAULT 'user', + creation_date IN acs_objects.creation_date%TYPE DEFAULT sysdate, + creation_user IN acs_objects.creation_user%TYPE DEFAULT NULL, + creation_ip IN acs_objects.creation_ip%TYPE DEFAULT NULL, + ... + ) RETURN users.user_id%TYPE + IS + v_user_id users.user_id%TYPE; + v_rel_id membership_rels.rel_id%TYPE; + BEGIN + v_user_id := acs_user.new (user_id, object_type, creation_date, + creation_user, creation_ip, email, ... + RETURN v_user_id; + END; +END acs; +/ +show errors + + + + + We port this to PostgreSQL by creating the different objects of + the Oracle package as functions with a standard naming + convention. We have to pay attention to some other details, like + the lack of default parameters in PostgreSQL functions. The above + package would become something like this: + + +CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...) +RETURNS INTEGER AS ' +DECLARE + user_id ALIAS FOR $1; + object_type ALIAS FOR $2; + creation_date ALIAS FOR $3; + creation_user ALIAS FOR $4; + creation_ip ALIAS FOR $5; + ... + v_user_id users.user_id%TYPE; + v_rel_id membership_rels.rel_id%TYPE; +BEGIN + v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...); + ... + + RETURN v_user_id; +END; +' LANGUAGE 'plpgsql'; + + + + + + + Other Things to Watch For + + + + EXECUTE + + + The PostgreSQL version of EXECUTE works + nicely, but you have to remember to use + quote_literal(TEXT) and + quote_string(TEXT) as described in . Constructs of the type + EXECUTE ''SELECT * from $1''; will not work + unless you use these functions. + + + + + Optimizing <application>PL/pgSQL</application> Functions + + + PostgreSQL gives you two function creation modifiers to optimize + execution: iscachable (function always returns + the same result when given the same arguments) and + isstrict (function returns NULL if any + argument is NULL). Consult the CREATE + FUNCTION reference for details. + + + + To make use of these optimization attributes, you have to use the + WITH modifier in your CREATE + FUNCTION statement. Something like: + + +CREATE FUNCTION foo(...) RETURNS INTEGER AS ' +... +' LANGUAGE 'plpgsql' +WITH (isstrict, iscachable); + + + + + + + + Appendix + + + + + Code for my <function>instr</function> functions + + + + This function should probably be integrated into the core. + + + +-- +-- instr functions that mimic Oracle's counterpart +-- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params. +-- +-- Searches string1 beginning at the nth character for the mth +-- occurrence of string2. If n is negative, search backwards. If m is +-- not passed, assume 1 (search starts at first character). +-- +-- by Roberto Mello (rmello@fslc.usu.edu) +-- modified by Robert Gaszewski (graszew@poland.com) +-- Licensed under the GPL v2 or later. +-- + +CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS ' +DECLARE + pos integer; +BEGIN + pos:= instr($1,$2,1); + RETURN pos; +END; +' LANGUAGE 'plpgsql'; + + +CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS ' +DECLARE + string ALIAS FOR $1; + string_to_search ALIAS FOR $2; + beg_index ALIAS FOR $3; + pos integer NOT NULL DEFAULT 0; + temp_str VARCHAR; + beg INTEGER; + length INTEGER; + ss_length INTEGER; +BEGIN + IF beg_index > 0 THEN + + temp_str := substring(string FROM beg_index); + pos := position(string_to_search IN temp_str); + + IF pos = 0 THEN + RETURN 0; + ELSE + RETURN pos + beg_index - 1; + END IF; + ELSE + ss_length := char_length(string_to_search); + length := char_length(string); + beg := length + beg_index - ss_length + 2; + + WHILE beg > 0 LOOP + temp_str := substring(string FROM beg FOR ss_length); + pos := position(string_to_search IN temp_str); + + IF pos > 0 THEN + RETURN beg; + END IF; + + beg := beg - 1; + END LOOP; + RETURN 0; + END IF; +END; +' LANGUAGE 'plpgsql'; + +-- +-- Written by Robert Gaszewski (graszew@poland.com) +-- Licensed under the GPL v2 or later. +-- +CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS ' +DECLARE + string ALIAS FOR $1; + string_to_search ALIAS FOR $2; + beg_index ALIAS FOR $3; + occur_index ALIAS FOR $4; + pos integer NOT NULL DEFAULT 0; + occur_number INTEGER NOT NULL DEFAULT 0; + temp_str VARCHAR; + beg INTEGER; + i INTEGER; + length INTEGER; + ss_length INTEGER; +BEGIN + IF beg_index > 0 THEN + beg := beg_index; + temp_str := substring(string FROM beg_index); + + FOR i IN 1..occur_index LOOP + pos := position(string_to_search IN temp_str); + + IF i = 1 THEN + beg := beg + pos - 1; + ELSE + beg := beg + pos; + END IF; + + temp_str := substring(string FROM beg + 1); + END LOOP; + + IF pos = 0 THEN + RETURN 0; + ELSE + RETURN beg; + END IF; + ELSE + ss_length := char_length(string_to_search); + length := char_length(string); + beg := length + beg_index - ss_length + 2; + + WHILE beg > 0 LOOP + temp_str := substring(string FROM beg FOR ss_length); + pos := position(string_to_search IN temp_str); + + IF pos > 0 THEN + occur_number := occur_number + 1; + + IF occur_number = occur_index THEN + RETURN beg; + END IF; + END IF; + + beg := beg - 1; + END LOOP; + + RETURN 0; + END IF; +END; +' LANGUAGE 'plpgsql'; + + + + +
+ +
+ + diff --git a/doc/src/sgml/plsql.sgml b/doc/src/sgml/plsql.sgml deleted file mode 100644 index 082b77fc5b2..00000000000 --- a/doc/src/sgml/plsql.sgml +++ /dev/null @@ -1,3001 +0,0 @@ - - - - <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language - - - PL/pgSQL - - - - PL/pgSQL is a loadable procedural language for the - PostgreSQL database system. - - - - This package was originally written by Jan Wieck. This - documentation was in part written - by Roberto Mello (rmello@fslc.usu.edu). - - - - Overview - - - 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. - - - - - - The PL/pgSQL call handler parses the function's source text and - produces an internal binary instruction tree the first time the - function is called (within any one backend process). The instruction tree - fully translates the - PL/pgSQL statement structure, but individual - SQL expressions and SQL queries - used in the function are not translated immediately. - - - As each expression and SQL query is first used - in the function, the PL/pgSQL interpreter creates a - prepared execution plan (using the SPI manager's - SPI_prepare and - SPI_saveplan functions). Subsequent visits - to that expression or query re-use the prepared plan. Thus, a function - with conditional code that contains many statements for which execution - plans might be required, will only prepare and save those plans - that are really used during the lifetime of the database - connection. This can provide a considerable savings of parsing - activity. A disadvantage is that errors in a specific expression - or query may not be detected until that part of the function is - reached in execution. - - - Once PL/pgSQL has made a query plan for a particular - query in a function, it will re-use that plan for the life of the - database connection. This is usually a win for performance, but it - can cause some problems if you dynamically - alter your database schema. For example: - - -CREATE FUNCTION populate() RETURNS INTEGER AS ' -DECLARE - -- Declarations -BEGIN - PERFORM my_function(); -END; -' LANGUAGE 'plpgsql'; - - If you execute the above function, it will reference the OID for - my_function() in the query plan produced for - the PERFORM statement. Later, if you - drop and re-create my_function(), then - populate() will not be able to find - my_function() anymore. You would then have to - re-create populate(), or at least start a new - database session so that it will be compiled afresh. - - - - Because PL/pgSQL saves execution plans in this way, queries that appear - directly in a PL/pgSQL function must refer to the same tables and fields - on every execution; that is, you cannot use a parameter as the name of - a table or field in a query. To get around - this restriction, you can construct dynamic queries using the PL/pgSQL - EXECUTE statement --- at the price of constructing a new query plan - on every execution. - - - Except for input/output conversion and calculation functions - for user defined types, anything that can be defined in C language - functions can also be done with PL/pgSQL. It is possible to - create complex conditional computation functions and later use - them to define operators or use them in functional indexes. - - - Advantages of Using PL/pgSQL - - - - - Better performance (see ) - - - - - - SQL support (see ) - - - - - - Portability (see ) - - - - - - Better Performance - - - SQL is the language PostgreSQL (and - most other Relational Databases) use as query - language. It's portable and easy to learn. But every - SQL statement must be executed - individually by the database server. - - - - That means that your client application must send each - query to the database server, wait for it to process it, - receive the results, do some computation, then send - other queries to the server. All this incurs inter-process communication - and may also incur network - overhead if your client is on a different machine than - the database server. - - - - With PL/pgSQL you can group a block of computation and a - series of queries inside the - database server, thus having the power of a procedural - language and the ease of use of SQL, but saving lots of - time because you don't have the whole client/server - communication overhead. This can make for a - considerable performance increase. - - - - - SQL Support - - - PL/pgSQL adds the power of a procedural language to the - flexibility and ease of SQL. With - PL/pgSQL you can use all the data types, columns, operators - and functions of SQL. - - - - - Portability - - - Because PL/pgSQL functions run inside PostgreSQL, these - functions will run on any platform where PostgreSQL - runs. Thus you can reuse code and have less development costs. - - - - - - Developing in <application>PL/pgSQL</application> - - - Developing in PL/pgSQL is pretty straight forward, especially - if you have developed in other database procedural languages, - such as Oracle's PL/SQL. Two good ways of developing in - PL/pgSQL are: - - - - - Using a text editor and reloading the file with psql - - - - - - Using PostgreSQL's GUI Tool: PgAccess - - - - - - - One good way to develop in PL/pgSQL is to simply use the text - editor of your choice to create your functions, and in another - console, use psql (PostgreSQL's interactive monitor) to load - those functions. If you are doing it this way, it is a good - idea to write the function using CREATE OR REPLACE - FUNCTION. That way you can reload the file to update - the function definition. For example: - -CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS ' - .... -end; -' LANGUAGE 'plpgsql'; - - - - - While running psql, you can load or reload such a - function definition file with - - \i filename.sql - - and then immediately issue SQL commands to test the function. - - - - Another good way to develop in PL/pgSQL is using - PostgreSQL's GUI tool: PgAccess. It does some - nice things for you, like escaping single-quotes, and making - it easy to recreate and debug functions. - - - - - - Structure of <application>PL/pgSQL</application> - - - PL/pgSQL is a block - structured language. The complete text of a function - definition must be a block. A block is defined as: - - - <<label>> - DECLARE - declarations -BEGIN - statements -END; - - - - - Any statement in the statement section of a block - can be a sub-block. Sub-blocks can be used for - logical grouping or to localize variables to a small group - of statements. - - - - The variables declared in the declarations section preceding a - block are initialized to their default values every time the - block is entered, not only once per function call. For example: - -CREATE FUNCTION somefunc() RETURNS INTEGER AS ' -DECLARE - quantity INTEGER := 30; -BEGIN - RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 30 - quantity := 50; - -- - -- Create a sub-block - -- - DECLARE - quantity INTEGER := 80; - BEGIN - RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 80 - END; - - RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 50 - - RETURN quantity; -END; -' LANGUAGE 'plpgsql'; - - - - - It is important not to confuse the use of BEGIN/END for - grouping statements in PL/pgSQL with the database commands for - transaction control. PL/pgSQL's BEGIN/END are only for grouping; - they do not start or end a transaction. Functions and trigger procedures - are always executed within a transaction established by an outer query - --- they cannot start or commit transactions, since - PostgreSQL does not have nested transactions. - - - - Lexical Details - - - Each statement and declaration within a block is terminated - by a semicolon. - - - - All keywords and identifiers can be written in mixed upper- and - lower-case. Identifiers are implicitly converted to lower-case - unless double-quoted. - - - - There are two types of comments in PL/pgSQL. A double dash -- - starts a comment that extends to the end of the line. A /* - starts a block comment that extends to the next occurrence of */. - Block comments cannot be nested, but double dash comments can be - enclosed into a block comment and a double dash can hide - the block comment delimiters /* and */. - - - - - - Declarations - - - All variables, rows and records used in a block must be declared in the - declarations section of the block. - (The only exception is that the loop variable of a FOR loop iterating - over a range of integer values is automatically declared as an integer - variable.) - - - - PL/pgSQL variables can have any SQL data type, such as - INTEGER, VARCHAR and - CHAR. - - - - Here are some examples of variable declarations: - -user_id INTEGER; -quantity NUMERIC(5); -url VARCHAR; -myrow tablename%ROWTYPE; -myfield tablename.fieldname%TYPE; -arow RECORD; - - - - - The general syntax of a variable declaration is: - -name CONSTANT type NOT NULL { DEFAULT | := } expression ; - - - - - The DEFAULT clause, if given, specifies the initial value assigned - to the variable when the block is entered. If the DEFAULT clause - is not given then the variable is initialized to the - SQL NULL value. - - - - The CONSTANT option prevents the variable from being assigned to, - so that its value remains constant for the duration of the block. - If NOT NULL - is specified, an assignment of a NULL value results in a run-time - error. All variables declared as NOT NULL - must have a non-NULL default value specified. - - - - The default value is evaluated every time the block is entered. So, - for example, assigning 'now' to a variable of type - timestamp causes the variable to have the - time of the current function call, not when the function was - precompiled. - - - - Examples: - -quantity INTEGER DEFAULT 32; -url varchar := ''http://mysite.com''; -user_id CONSTANT INTEGER := 10; - - - - - Aliases for Function Parameters - - - -name ALIAS FOR $n; - - - - - Parameters passed to functions are named with the identifiers - $1, $2, - etc. Optionally, aliases can be declared for $n - parameter names for increased readability. Either the alias or the - numeric identifier can then be used to refer to the parameter value. - Some examples: - -CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS ' -DECLARE - subtotal ALIAS FOR $1; -BEGIN - return subtotal * 0.06; -END; -' LANGUAGE 'plpgsql'; - - -CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS ' -DECLARE - v_string ALIAS FOR $1; - index ALIAS FOR $2; -BEGIN - -- Some computations here -END; -' LANGUAGE 'plpgsql'; - - -CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS ' -DECLARE - in_t ALIAS FOR $1; -BEGIN - RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; -END; -' LANGUAGE 'plpgsql'; - - - - - - Row Types - - - -name tablename%ROWTYPE; - - - - - A variable of a composite type is called a row - variable (or row-type variable). Such a variable can hold a - whole row of a SELECT or FOR - query result, so long as that query's column set matches the declared - type of the variable. The individual fields of the row value are - accessed using the usual dot notation, for example - rowvar.field. - - - - Presently, a row variable can only be declared using the - %ROWTYPE notation; although one might expect a - bare table name to work as a type declaration, it won't be accepted - within PL/pgSQL functions. - - - - Parameters to a function can be - composite types (complete table rows). In that case, the - corresponding identifier $n will be a row variable, and fields can - be selected from it, for example $1.user_id. - - - - Only the user-defined attributes of a table row are accessible in a - row-type variable, not OID or other system attributes (because the - row could be from a view). The fields of the row type inherit the - table's field size or precision for data types such as - char(n). - -CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS ' -DECLARE - in_t ALIAS FOR $1; - use_t table2name%ROWTYPE; -BEGIN - SELECT * INTO use_t FROM table2name WHERE ... ; - RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7; -END; -' LANGUAGE 'plpgsql'; - - - - - - Records - - - -name RECORD; - - - - - Record variables are similar to row-type variables, but they have no - predefined structure. They take on the actual row structure of the - row they are assigned during a SELECT or FOR command. The substructure - of a record variable can change each time it is assigned to. - A consequence of this is that until a record variable is first assigned - to, it has no substructure, and any attempt to access a - field in it will draw a run-time error. - - - - Note that RECORD is not a true data type, only a placeholder. - Thus, for example, one cannot declare a function returning - RECORD. - - - - - Attributes - - - Using the %TYPE and %ROWTYPE - attributes, you can declare variables with the same - data type or structure as another database item (e.g: a - table field). - - - - - - variable%TYPE - - - - %TYPE provides the data type of a - variable or database column. You can use this to - declare variables that will hold database - values. For example, let's say you have a column - named user_id in your - users table. To declare a variable with - the same data type as users.user_id you write: - -user_id users.user_id%TYPE; - - - - - By using %TYPE you don't need to know - the data type of the structure you are referencing, - and most important, if the data type of the - referenced item changes in the future (e.g: you - change your table definition of user_id from INTEGER to - REAL), you may not need to change your function - definition. - - - - - - - table%ROWTYPE - - - - %ROWTYPE provides the composite data type corresponding - to a whole row of the specified table. - table must be an existing - table or view name of the database. - - - -DECLARE - users_rec users%ROWTYPE; - user_id users.user_id%TYPE; -BEGIN - user_id := users_rec.user_id; - ... - -CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS ' - DECLARE - key ALIAS FOR $1; - table_data cs_materialized_views%ROWTYPE; - BEGIN - SELECT INTO table_data * FROM cs_materialized_views - WHERE sort_key=key; - - IF NOT FOUND THEN - RETURN false; - END IF; - RETURN true; - END; -' LANGUAGE 'plpgsql'; - - - - - - - - RENAME - - - -RENAME oldname TO newname; - - - Using the RENAME declaration you can change the name of a variable, - record or row. This is primarily useful if NEW or OLD should be - referenced by another name inside a trigger procedure. See also ALIAS. - - - - Examples: - -RENAME id TO user_id; -RENAME this_var TO that_var; - - - - - - RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is - of low priority, since ALIAS covers most of the practical uses of - RENAME. - - - - - - - - Expressions - - - All expressions used in PL/pgSQL statements - are processed using the server's regular SQL executor. Expressions that - appear to contain - constants may in fact require run-time evaluation - (e.g. 'now' for the - timestamp type) so - it is impossible for the PL/pgSQL parser - to identify real constant values other than the NULL keyword. All - expressions are evaluated internally by executing a query - -SELECT expression - - using the SPI manager. In the expression, occurrences - of PL/pgSQL variable - identifiers are replaced by parameters and the actual values from - the variables are passed to the executor in the parameter array. - This allows the query plan for the SELECT to be prepared just once - and then re-used for subsequent evaluations. - - - - The evaluation done by the PostgreSQL - main parser has some side - effects on the interpretation of constant values. In detail there - is a difference between what these two functions do: - - -CREATE FUNCTION logfunc1 (TEXT) RETURNS TIMESTAMP AS ' - DECLARE - logtxt ALIAS FOR $1; - BEGIN - INSERT INTO logtable VALUES (logtxt, ''now''); - RETURN ''now''; - END; -' LANGUAGE 'plpgsql'; - - - and - - -CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS ' - DECLARE - logtxt ALIAS FOR $1; - curtime timestamp; - BEGIN - curtime := ''now''; - INSERT INTO logtable VALUES (logtxt, curtime); - RETURN curtime; - END; -' LANGUAGE 'plpgsql'; - - - In the case of logfunc1(), the - PostgreSQL main parser knows when - preparing the plan for the INSERT, that the string - 'now' should be interpreted as - timestamp because the target field of logtable - is of that type. Thus, it will make a constant from it at this - time and this constant value is then used in all invocations of - logfunc1() during the lifetime of the - backend. Needless to say that this isn't what the - programmer wanted. - - - - In the case of logfunc2(), the - PostgreSQL main parser does not know - what type 'now' should become and therefore - it returns a data value of type text containing the string - 'now'. During the ensuing assignment - to the local variable curtime, the - PL/pgSQL interpreter casts this - string to the timestamp type by calling the - text_out() and timestamp_in() - functions for the conversion. So, the computed time stamp is updated - on each execution as the programmer expects. - - - - The mutable nature of record variables presents a problem in this - connection. When fields of a record variable are used in expressions or - statements, the data types of the - fields must not change between calls of one and the same expression, - since the expression will be planned using the data type that is present - when the expression is first reached. - Keep this in mind when writing trigger procedures that handle events - for more than one table. (EXECUTE can be used to get around this - problem when necessary.) - - - - - Basic Statements - - - In this section and the following ones, we describe all the statement - types that are explicitly understood by - PL/pgSQL. - Anything not recognized as one of these statement types is presumed - to be an SQL query, and is sent to the main database engine to execute - (after substitution for any PL/pgSQL variables - used in the statement). Thus, - for example, SQL INSERT, UPDATE, and - DELETE commands may be considered to be statements of - PL/pgSQL. But they are not specifically - listed here. - - - - Assignment - - - An assignment of a value to a variable or row/record field is - written as: - -identifier := expression; - - As explained above, the expression in such a statement is evaluated - by means of an SQL SELECT command sent to the main - database engine. The expression must yield a single value. - - - - If the expression's result data type doesn't match the variable's - data type, or the variable has a specific size/precision - (like char(20)), the result value will be implicitly - converted by the PL/pgSQL interpreter using - the result type's output-function and - the variable type's input-function. Note that this could potentially - result in run-time errors generated by the input function, if the - string form of the result value is not acceptable to the input function. - - - - Examples: - -user_id := 20; -tax := subtotal * 0.06; - - - - - - SELECT INTO - - - The result of a SELECT command yielding multiple columns (but - only one row) can be assigned to a record variable, row-type - variable, or list of scalar variables. This is done by: - - -SELECT INTO target expressions FROM ...; - - - where target can be a record variable, a row - variable, or a comma-separated list of simple variables and - record/row fields. Note that this is quite different from - PostgreSQL's normal interpretation of SELECT INTO, which is that the - INTO target is a newly created table. (If you want to create a - table from a SELECT result inside a PL/pgSQL function, use the - syntax CREATE TABLE ... AS SELECT.) - - - - If a row or a variable list is used as target, the selected values - must exactly match the structure of the target(s), or a run-time error - occurs. When a record variable is the target, it automatically - configures itself to the row type of the query result columns. - - - - Except for the INTO clause, the SELECT statement is the same as a normal - SQL SELECT query and can use the full power of SELECT. - - - - If the SELECT query returns zero rows, NULLs are assigned to the - target(s). If the SELECT query returns multiple rows, the first - row is assigned to the target(s) and the rest are discarded. - (Note that the first row is not well-defined unless you've - used ORDER BY.) - - - - At present, the INTO clause can appear almost anywhere in the SELECT - query, but it is recommended to place it immediately after the SELECT - keyword as depicted above. Future versions of - PL/pgSQL may be less forgiving about - placement of the INTO clause. - - - - There is a special variable named FOUND of type - boolean that can be used immediately after a SELECT - INTO to check if an assignment had success (that is, at least one - row was returned by the SELECT). For example, - - -SELECT INTO myrec * FROM EMP WHERE empname = myname; -IF NOT FOUND THEN - RAISE EXCEPTION ''employee % not found'', myname; -END IF; - - - Alternatively, you can use the IS NULL (or ISNULL) conditional to - test for NULLity of a RECORD/ROW result. Note that there is no - way to tell whether any additional rows might have been discarded. - - - - -DECLARE - users_rec RECORD; - full_name varchar; -BEGIN - SELECT INTO users_rec * FROM users WHERE user_id=3; - - IF users_rec.homepage IS NULL THEN - -- user entered no homepage, return "http://" - - RETURN ''http://''; - END IF; -END; - - - - - - Executing an expression or query with no result - - - Sometimes one wishes to evaluate an expression or query but discard - the result (typically because one is calling a function that has - useful side-effects but no useful result value). To do this in - PL/pgSQL, use the PERFORM statement: - - -PERFORM query; - - - This executes a SELECT - query and discards the - result. PL/pgSQL variables are substituted - in the query as usual. Also, the special variable FOUND is set to - true if the query produced at least one row, or false if it produced - no rows. - - - - - One might expect that SELECT with no INTO clause would accomplish - this result, but at present the only accepted way to do it is PERFORM. - - - - - An example: - -PERFORM create_mv(''cs_session_page_requests_mv'', my_query); - - - - - - Executing dynamic queries - - - Oftentimes you will want to generate dynamic queries inside - your PL/pgSQL functions, that is, - queries that will involve different tables or different data types - each time they are executed. PL/pgSQL's - normal attempts to cache plans for queries will not work in such - scenarios. To handle this sort of problem, the EXECUTE statement - is provided: - - -EXECUTE query-string; - - - where query-string is an expression - yielding a string (of type - text) containing the query - to be executed. This string is fed literally to the SQL engine. - - - - Note in particular that no substitution of PL/pgSQL - variables is done on the query string. The values of variables must - be inserted in the query string as it is constructed. - - - - When working with dynamic queries you will have to face - escaping of single quotes in PL/pgSQL. Please refer to the - table in - for a detailed explanation that will save you some effort. - - - - Unlike all other queries in PL/pgSQL, a - query run by an EXECUTE statement is - not prepared and saved just once during the life of the server. - Instead, the query is prepared each - time the statement is run. The - query-string can be dynamically - created within the procedure to perform actions on variable - tables and fields. - - - - The results from SELECT queries are discarded by EXECUTE, and - SELECT INTO is not currently supported within EXECUTE. So, the - only way to extract a result from a dynamically-created SELECT is - to use the FOR-IN-EXECUTE form described later. - - - - An example: - -EXECUTE ''UPDATE tbl SET '' - || quote_ident(fieldname) - || '' = '' - || quote_literal(newvalue) - || '' WHERE ...''; - - - - - This example shows use of the functions - quote_ident(TEXT) and - quote_literal(TEXT). - Variables containing field and table identifiers should be - passed to function quote_ident(). - Variables containing literal elements of the dynamic query - string should be passed to - quote_literal(). Both take the - appropriate steps to return the input text enclosed in single - or double quotes and with any embedded special characters - properly escaped. - - - - Here is a much larger example of a dynamic query and EXECUTE: - -CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' -DECLARE - referrer_keys RECORD; -- Declare a generic record to be used in a FOR - a_output varchar(4000); -BEGIN - a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) - RETURNS VARCHAR AS '''' - DECLARE - v_host ALIAS FOR $1; - v_domain ALIAS FOR $2; - v_url ALIAS FOR $3; - BEGIN ''; - - -- - -- Notice how we scan through the results of a query in a FOR loop - -- using the FOR <record> construct. - -- - - FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP - a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' - || referrer_keys.key_string || '''''''''' THEN RETURN '''''' - || referrer_keys.referrer_type || ''''''; END IF;''; - END LOOP; - - a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';''; - - -- This works because we are not substituting any variables - -- Otherwise it would fail. Look at PERFORM for another way to run functions - - EXECUTE a_output; -END; -' LANGUAGE 'plpgsql'; - - - - - - Obtaining result status - - - -GET DIAGNOSTICS variable = item , ... ; - - - This command allows retrieval of system status indicators. Each - item is a keyword identifying a state - value to be assigned to the specified variable (which should be - of the right data type to receive it). The currently available - status items are ROW_COUNT, the number of rows - processed by the last SQL query sent down to - the SQL engine; and RESULT_OID, - the OID of the last row inserted by the most recent - SQL query. Note that RESULT_OID - is only useful after an INSERT query. - - - - - - Control Structures - - - 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. - - - - Returning from a function - - - -RETURN expression; - - - The function terminates and the value of - expression will be returned to the - upper executor. - The expression's result will be automatically cast into the - function's return type as described for assignments. - - - - The return value of a function cannot be left undefined. If control - reaches the end of the top-level block of - the function without hitting a RETURN statement, a run-time error - will occur. - - - - - Conditionals - - - IF statements let you execute commands based on - certain conditions. - PL/pgSQL has four forms of IF: IF-THEN, IF-THEN-ELSE, - IF-THEN-ELSE IF, and IF-THEN-ELSIF-THEN-ELSE. - - - - IF-THEN - - - -IF boolean-expression THEN - statements -END IF; - - - IF-THEN statements are the simplest form of IF. The - statements between THEN and END IF will be executed if - the condition is true. Otherwise, they are skipped. - - -IF v_user_id <> 0 THEN - UPDATE users SET email = v_email WHERE user_id = v_user_id; -END IF; - - - - - - IF-THEN-ELSE - - - -IF boolean-expression THEN - statements -ELSE - statements -END IF; - - - IF-THEN-ELSE statements add to IF-THEN by letting you - specify an alternative set of statements that should be executed if - the condition evaluates to FALSE. - - -IF parentid IS NULL or parentid = '''' -THEN - return fullname; -ELSE - return hp_true_filename(parentid) || ''/'' || fullname; -END IF; - - -IF v_count > 0 THEN - INSERT INTO users_count(count) VALUES(v_count); - return ''t''; -ELSE - return ''f''; -END IF; - - - - - - IF-THEN-ELSE IF - - - IF statements can be nested, as in the following example: - -IF demo_row.sex = ''m'' THEN - pretty_sex := ''man''; -ELSE - IF demo_row.sex = ''f'' THEN - pretty_sex := ''woman''; - END IF; -END IF; - - - - - When you use this form, you are actually - nesting an IF statement inside the ELSE part of an outer IF - statement. Thus you need one END IF statement for each - nested IF and one for the parent IF-ELSE. - This is workable but grows tedious when there are many - alternatives to be checked. - - - - - IF-THEN-ELSIF-ELSE - - - -IF boolean-expression THEN - statements - ELSIF boolean-expression THEN - statements - ELSIF boolean-expression THEN - statements - ... - - - ELSE - statements -END IF; - - - IF-THEN-ELSIF-ELSE provides a more convenient method of checking - many alternatives in one statement. Formally it is equivalent - to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF - is needed. - - - - Here is an example: - - -IF number = 0 THEN - result := ''zero''; -ELSIF number > 0 THEN - result := ''positive''; -ELSIF number < 0 THEN - result := ''negative''; -ELSE - -- hmm, the only other possibility is that number IS NULL - result := ''NULL''; -END IF; - - - - - The final ELSE section is optional. - - - - - - - Simple Loops - - - With the LOOP, EXIT, WHILE and FOR statements, you can arrange - for your PL/pgSQL function to repeat - a series of commands. - - - - LOOP - - - -<<label>> -LOOP - statements -END LOOP; - - - LOOP defines an unconditional loop that is repeated indefinitely - until terminated by an EXIT or RETURN statement. - The optional label can be used by - EXIT statements in nested loops to specify which level of - nesting should be terminated. - - - - - EXIT - - - -EXIT label WHEN expression ; - - - If no label is given, - the innermost loop is terminated and the - statement following END LOOP is executed next. - If label is 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 corresponding - END. - - - - If WHEN is present, loop exit occurs only if the specified condition - is true, otherwise control passes to the statement after EXIT. - - - - Examples: - -LOOP - -- some computations - IF count > 0 THEN - EXIT; -- exit loop - END IF; -END LOOP; - -LOOP - -- some computations - EXIT WHEN count > 0; -END LOOP; - -BEGIN - -- some computations - IF stocks > 100000 THEN - EXIT; -- illegal. Can't use EXIT outside of a LOOP - END IF; -END; - - - - - - WHILE - - - -<<label>> -WHILE expression LOOP - statements -END LOOP; - - - The WHILE statement repeats a - sequence of statements so long as the condition expression - evaluates to true. The condition is checked just before - each entry to the loop body. - - - - For example: - -WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP - -- some computations here -END LOOP; - -WHILE NOT boolean_expression LOOP - -- some computations here -END LOOP; - - - - - - FOR (integer for-loop) - - - -<<label>> -FOR name IN REVERSE expression .. expression LOOP - statements -END LOOP; - - - This form of FOR creates a loop that iterates over a range of integer - values. The variable - name is automatically defined as type - integer and exists only inside the loop. The two expressions giving - the lower and upper bound of the range are evaluated once when entering - the loop. The iteration step is normally 1, but is -1 when REVERSE is - specified. - - - - Some examples of integer FOR loops: - -FOR i IN 1..10 LOOP - -- some expressions here - - RAISE NOTICE ''i is %'',i; -END LOOP; - -FOR i IN REVERSE 10..1 LOOP - -- some expressions here -END LOOP; - - - - - - - Looping Through Query Results - - - Using a different type of FOR loop, you can iterate through - the results of a query and manipulate that data - accordingly. The syntax is: - -<<label>> -FOR record | row IN select_query LOOP - statements -END LOOP; - - The record or row variable is successively assigned all the rows - resulting from the SELECT query and the loop body is executed - for each row. Here is an example: - - - - -CREATE FUNCTION cs_refresh_mviews () RETURNS INTEGER AS ' -DECLARE - mviews RECORD; -BEGIN - PERFORM cs_log(''Refreshing materialized views...''); - - FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP - - -- Now "mviews" has one record from cs_materialized_views - - PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...''); - EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name); - EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query; - END LOOP; - - PERFORM cs_log(''Done refreshing materialized views.''); - RETURN 1; -end; -' LANGUAGE 'plpgsql'; - - - If the loop is terminated by an EXIT statement, the last - assigned row value is still accessible after the loop. - - - - The FOR-IN-EXECUTE statement is another way to iterate over - records: - -<<label>> -FOR record | row IN EXECUTE text_expression LOOP - statements -END LOOP; - - This is like the previous form, except that the source SELECT - statement is specified as a string expression, which is evaluated - and re-planned on each entry to the FOR loop. This allows the - programmer to choose the speed of a pre-planned query or the - flexibility of a dynamic query, just as with a plain EXECUTE - statement. - - - - - The PL/pgSQL parser presently distinguishes the - two kinds of FOR loops (integer or record-returning) by checking - whether the target variable mentioned just after FOR has been - declared as a record/row variable. If not, it's presumed to be - an integer FOR loop. This can cause rather nonintuitive error - messages when the true problem is, say, that one has - misspelled the FOR variable name. - - - - - - - Cursors - - - 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. One reason for doing this is - to avoid memory overrun when the result contains a large number of - rows. (However, PL/pgSQL users don't normally need - to worry about that, since FOR loops automatically use a cursor - internally to avoid memory problems.) A more interesting usage is to - return a reference to a cursor that it has created, allowing the - caller to read the rows. This provides a way to return row sets - from functions. - - - - Declaring Cursor Variables - - - All access to cursors in PL/pgSQL goes through - cursor variables, which are always of the special data type - refcursor. One way to create a cursor variable - is just to declare it as a variable of type refcursor. - Another way is to use the cursor declaration syntax, - which in general is: - -name CURSOR ( arguments ) FOR select_query ; - - (FOR may be replaced by IS for Oracle - compatibility.) arguments, if any, - are a comma-separated list of name - datatype pairs that define names to - be replaced by parameter values in the given query. The actual - values to substitute for these names will be specified later, - when the cursor is opened. - - - Some examples: - -DECLARE - curs1 refcursor; - curs2 CURSOR FOR SELECT * from tenk1; - curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key; - - All three of these variables have the data type refcursor, - but the first may 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. (key will be - replaced by an integer parameter value when the cursor is opened.) - The variable curs1 - is said to be unbound since it is not bound to - any particular query. - - - - - Opening Cursors - - - Before a cursor can be used to retrieve rows, it must be - opened. (This is the equivalent action to the SQL - command DECLARE CURSOR.) PL/pgSQL has - four forms of the OPEN statement, two of which use unbound cursor - variables and the other two use bound cursor variables. - - - - OPEN FOR SELECT - - - -OPEN unbound-cursor FOR SELECT ...; - - - The cursor variable is opened and given the specified query - to execute. The cursor cannot be open already, and it must - have been declared as an unbound cursor (that is, as a simple - refcursor variable). The SELECT query is treated - in the same way as other SELECTs in PL/pgSQL: - PL/pgSQL variable names are substituted, - and the query plan is cached for possible re-use. - - -OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; - - - - - - OPEN FOR EXECUTE - - - -OPEN unbound-cursor FOR EXECUTE query-string; - - - The cursor variable is opened and given the specified query - to execute. The cursor cannot be open already, and it must - have been declared as an unbound cursor (that is, as a simple - refcursor variable). The query is specified as a - string expression in the same way as in the EXECUTE command. - As usual, this gives flexibility so the query can vary - from one run to the next. - - -OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1); - - - - - - OPENing a bound cursor - - - -OPEN bound-cursor ( argument_values ) ; - - - This form of OPEN is used to open a cursor variable whose query - was bound to it when it was declared. - The cursor cannot be open already. 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. - The query plan for a bound cursor is always considered - cacheable --- there is no equivalent of EXECUTE in this case. - - -OPEN curs2; -OPEN curs3(42); - - - - - - - Using Cursors - - - Once a cursor has been opened, it can be manipulated with the - statements described here. - - - - These manipulations need not occur in the same function that - opened the cursor to begin with. You can return a refcursor - value out of a function and let the caller operate on the cursor. - (Internally, a refcursor value is simply the string name - of a Portal containing the active query for the cursor. This name - can be passed around, assigned to other refcursor variables, - and so on, without disturbing the Portal.) - - - - All Portals are implicitly closed at transaction end. Therefore - a refcursor value is useful to reference an open cursor - only until the end of the transaction. - - - - FETCH - - - -FETCH cursor INTO target; - - - FETCH retrieves the next row from the cursor into a target, - which may be a row variable, a record variable, or a comma-separated - list of simple variables, just like SELECT INTO. As with - SELECT INTO, the special variable FOUND may be checked to see - whether a row was obtained or not. - - -FETCH curs1 INTO rowvar; -FETCH curs2 INTO foo,bar,baz; - - - - - - CLOSE - - - -CLOSE cursor; - - - CLOSE closes 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 again. - - -CLOSE curs1; - - - - - - Returning Cursors - - - - PL/pgSQL functions can return cursors to the - caller. This is used to return multiple rows or columns from the - function. The function opens the cursor and returns the cursor - name to the caller. The caller can then FETCH rows from the - cursor. The cursor can be CLOSEd by the caller, or it will be - closed automatically when the transaction closes. - - - - - The cursor name returned by the function can be specified by the - caller or automatically generated. The following example shows - how a cursor name can be supplied by the caller: - - -CREATE TABLE test (col text); -INSERT INTO test VALUES ('123'); - -CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' -BEGIN - OPEN $1 FOR SELECT col FROM test; - RETURN $1; -END; -' LANGUAGE 'plpgsql'; - -BEGIN; -SELECT reffunc('funccursor'); -FETCH ALL IN funccursor; -COMMIT; - - - - - The following example uses automatic cursor name generation: - - -CREATE FUNCTION reffunc2() RETURNS refcursor AS ' -DECLARE - ref refcursor; -BEGIN - OPEN ref FOR SELECT col FROM test; - RETURN ref; -END; -' LANGUAGE 'plpgsql'; - -BEGIN; -SELECT reffunc2(); - - reffunc2 - -------------------- - <unnamed cursor 1> - (1 row) - -FETCH ALL IN "<unnamed cursor 1>"; -COMMIT; - - - - - - - - Errors and Messages - - - Use the RAISE statement to report messages and raise errors. - - -RAISE level 'format' , variable ...; - - - Possible levels are DEBUG (write the message into the postmaster log), - NOTICE (write the message into the postmaster log and forward it to - the client application) and EXCEPTION (raise an error, - aborting the transaction). - - - - Inside the format string, % is replaced by the next - optional argument's external representation. - Write %% to emit a literal %. - Note that the optional arguments must presently - be simple variables, not expressions, and the format must be a simple - string literal. - - - - - - Examples: - -RAISE NOTICE ''Calling cs_create_job(%)'',v_job_id; - - In this example, the value of v_job_id will replace the % in the - string. - - - - -RAISE EXCEPTION ''Inexistent ID --> %'',user_id; - - This will abort the transaction with the given error message. - - - - Exceptions - - - PostgreSQL does not have a very smart - exception handling model. Whenever the parser, planner/optimizer - or executor decide that a statement cannot be processed any longer, - the whole transaction gets aborted and the system jumps back - into the main loop to get the next query from the client application. - - - - It is possible to hook into the error mechanism to notice that this - happens. But currently it is impossible to tell what really - caused the abort (input/output conversion error, floating-point - error, parse error). And it is possible that the database backend - is in an inconsistent state at this point so returning to the upper - executor or issuing more commands might corrupt the whole database. - - - - Thus, the only thing PL/pgSQL currently does when it encounters - an abort during execution of a function or trigger - procedure is to write some additional NOTICE level log messages - telling in which function and where (line number and type of - statement) this happened. The error always stops execution of - the function. - - - - - - Trigger Procedures - - - PL/pgSQL can be used to define trigger - procedures. A trigger procedure is created with the CREATE - FUNCTION command as a function with no arguments and a return - type of OPAQUE. 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: - - - - NEW - - - Data type RECORD; variable holding the new database row for INSERT/UPDATE - operations in ROW level triggers. - - - - - - OLD - - - Data type RECORD; variable holding the old database row for UPDATE/DELETE - operations in ROW level triggers. - - - - - - TG_NAME - - - Data type name; variable that contains the name of the trigger actually - fired. - - - - - - TG_WHEN - - - Data type text; a string of either - BEFORE or AFTER - depending on the trigger's definition. - - - - - - TG_LEVEL - - - Data type text; a string of either - ROW or STATEMENT depending on the - trigger's definition. - - - - - - TG_OP - - - Data type text; a string of - INSERT, UPDATE - or DELETE telling - for which operation the trigger is fired. - - - - - - TG_RELID - - - Data type oid; the object ID of the table that caused the - trigger invocation. - - - - - - TG_RELNAME - - - Data type name; the name of the table that caused the trigger - invocation. - - - - - - TG_NARGS - - - Data type integer; the number of arguments given to the trigger - procedure in the CREATE TRIGGER statement. - - - - - - TG_ARGV[] - - - Data type array of text; the arguments from - the CREATE TRIGGER statement. - The index counts from 0 and can be given as an expression. Invalid - indices (< 0 or >= tg_nargs) result in a NULL value. - - - - - - - - A trigger function must return either NULL or a record/row value - having exactly the structure of the table the trigger was fired for. - Triggers fired BEFORE may return NULL to signal the trigger manager - to skip the rest of the operation for this row (ie, subsequent triggers - are not fired, and the INSERT/UPDATE/DELETE does not occur for this - row). If a non-NULL value is returned then the operation proceeds with - that row value. Note that returning a row value different from the - original value of NEW alters the row that will be inserted or updated. - It is possible to replace single values directly - in NEW and return that, or to build a complete new record/row to - return. - - - - The return value of a trigger fired AFTER is ignored; it may as well - always return a NULL value. But an AFTER trigger can still abort the - operation by raising an error. - - - - A <application>PL/pgSQL</application> Trigger Procedure Example - - - This example trigger ensures that any time a row is inserted or updated - in the table, the current user name and time are stamped into the - row. And it ensures that an employee's name is given and that the - salary is a positive value. - - -CREATE TABLE emp ( - empname text, - salary integer, - last_date timestamp, - last_user text -); - -CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' - BEGIN - -- Check that empname and salary are given - IF NEW.empname ISNULL THEN - RAISE EXCEPTION ''empname cannot be NULL value''; - END IF; - IF NEW.salary ISNULL THEN - RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; - END IF; - - -- Who works for us when she must pay for? - IF NEW.salary < 0 THEN - RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; - END IF; - - -- Remember who changed the payroll when - NEW.last_date := ''now''; - NEW.last_user := current_user; - RETURN NEW; - END; -' LANGUAGE 'plpgsql'; - -CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp - FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); - - - - - - - - - Examples - - - Here are only a few functions to demonstrate how easy it is to - write PL/pgSQL - functions. For more complex examples the programmer - might look at the regression test for PL/pgSQL. - - - - One painful detail in writing functions in PL/pgSQL is the handling - of single quotes. The function's source text in CREATE FUNCTION must - be a literal string. Single quotes inside of literal strings must be - either doubled or quoted with a backslash. We are still looking for - an elegant alternative. In the meantime, doubling the single quotes - as in the examples below should be used. Any solution for this - in future versions of PostgreSQL will be - forward compatible. - - - - For a detailed explanation and examples of how to escape single - quotes in different situations, please see . - - - - A Simple <application>PL/pgSQL</application> Function to Increment an Integer - - - The following two PL/pgSQL functions are identical to their - counterparts from the C language function discussion. This - function receives an integer and increments it by - one, returning the incremented value. - - - -CREATE FUNCTION add_one (integer) RETURNS INTEGER AS ' - BEGIN - RETURN $1 + 1; - END; -' LANGUAGE 'plpgsql'; - - - - - A Simple <application>PL/pgSQL</application> Function to Concatenate Text - - - This function receives two text parameters and - returns the result of concatenating them. - - - -CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS ' - BEGIN - RETURN $1 || $2; - END; -' LANGUAGE 'plpgsql'; - - - - - A <application>PL/pgSQL</application> Function on Composite Type - - - In this example, we take EMP (a table) and an - integer as arguments to our function, which returns - a boolean. If the salary field of the EMP table is - NULL, we return f. Otherwise we compare with - that field with the integer passed to the function - and return the boolean result of the comparison (t - or f). This is the PL/pgSQL equivalent to the example from the C - functions. - - - -CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS ' - DECLARE - emprec ALIAS FOR $1; - sallim ALIAS FOR $2; - BEGIN - IF emprec.salary ISNULL THEN - RETURN ''f''; - END IF; - RETURN emprec.salary > sallim; - END; -' LANGUAGE 'plpgsql'; - - - - - - - - - - - February 2001 - - - Roberto - Mello - -
- rmello@fslc.usu.edu -
-
-
- -
- - Porting from Oracle PL/SQL - - - Oracle - - - - PL/SQL - - - - Author - - Roberto Mello (rmello@fslc.usu.edu) - - - - - This section explains differences between Oracle's PL/SQL and - PostgreSQL's PL/pgSQL languages in the hopes of helping developers - port applications from Oracle to PostgreSQL. Most of the code here - is from the ArsDigita - Clickstream - module that I ported to PostgreSQL when I took an - internship with OpenForce - Inc. in the Summer of 2000. - - - - PL/pgSQL is similar to PL/SQL in many aspects. It is a block - structured, imperative language (all variables have to be - declared). PL/SQL has many more features than its PostgreSQL - counterpart, but PL/pgSQL allows for a great deal of functionality - and it is being improved constantly. - - - - Main Differences - - - Some things you should keep in mind when porting from Oracle to PostgreSQL: - - - - - No default parameters in PostgreSQL. - - - - - - You can overload functions in PostgreSQL. This is often used to work - around the lack of default parameters. - - - - - - Assignments, loops and conditionals are similar. - - - - - - No need for cursors in PostgreSQL, just put the query in the FOR - statement (see example below) - - - - - - In PostgreSQL you need to escape single - quotes. See . - - - - - - - Quote Me on That: Escaping Single Quotes - - - In PostgreSQL you need to escape single quotes inside your - function definition. This can lead to quite amusing code at - times, especially if you are creating a function that generates - other function(s), as in - . - One thing to keep in mind - when escaping lots of single quotes is that, except for the - beginning/ending quotes, all the others will come in even - quantity. - - - - gives the scoop. (You'll - love this little chart.) - - - - Single Quotes Escaping Chart - - - - - No. of Quotes - Usage - Example - Result - - - - - - 1 - To begin/terminate function bodies - -CREATE FUNCTION foo() RETURNS INTEGER AS '...' -LANGUAGE 'plpgsql'; - - as is - - - - 2 - In assignments, SELECTs, to delimit strings, etc. - -a_output := ''Blah''; -SELECT * FROM users WHERE f_name=''foobar''; - - SELECT * FROM users WHERE f_name='foobar'; - - - - 4 - - When you need two single quotes in your resulting string - without terminating that string. - - -a_output := a_output || '' AND name - LIKE ''''foobar'''' AND ...'' - - AND name LIKE 'foobar' AND ... - - - - 6 - - When you want double quotes in your resulting string - and terminate that string. - - -a_output := a_output || '' AND name - LIKE ''''foobar'''''' - - - AND name LIKE 'foobar' - - - - - 10 - - When you want two single quotes in the resulting string - (which accounts for 8 quotes) and - terminate that string (2 more). You will probably only need - that if you were using a function to generate other functions - (like in ). - - -a_output := a_output || '' if v_'' || - referrer_keys.kind || '' like '''''''''' - || referrer_keys.key_string || '''''''''' - then return '''''' || referrer_keys.referrer_type - || ''''''; end if;''; - - - if v_<...> like ''<...>'' then return ''<...>''; end if; - - - - -
-
-
- - - - Porting Functions - - - - - A Simple Function - - - - Here is an Oracle function: - -CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN 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 to PL/pgSQL: - - - - - PostgreSQL does not have named - parameters. You have to explicitly alias them inside your - function. - - - - - - Oracle can have IN, OUT, - and INOUT parameters passed to functions. - The INOUT, for example, means that the - parameter will receive a value and return another. PostgreSQL - only has IN parameters and functions can return - only a single value. - - - - - - The RETURN key word in the function - prototype (not the function body) becomes - RETURNS in PostgreSQL. - - - - - - On PostgreSQL functions are created using single quotes as - delimiters, so you have to escape single quotes inside your - functions (which can be quite annoying at times; see ). - - - - - - The /show errors command does not exist in - PostgreSQL. - - - - - - - So let's see how this function would look when ported to - PostgreSQL: - - -CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR) -RETURNS VARCHAR AS ' -DECLARE - v_name ALIAS FOR $1; - v_version ALIAS FOR $2; -BEGIN - IF v_version IS NULL THEN - return v_name; - END IF; - RETURN v_name || ''/'' || v_version; -END; -' LANGUAGE 'plpgsql'; - - - - - - - A Function that Creates Another Function - - - - The following procedure grabs rows from a - SELECT statement and builds a large function - with the results in IF statements, for the - sake of efficiency. Notice particularly the differences in - cursors, FOR loops, and the need to escape - single quotes in PostgreSQL. - - -CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS - CURSOR referrer_keys IS - SELECT * FROM cs_referrer_keys - ORDER BY try_order; - - a_output VARCHAR(4000); -BEGIN - a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR, -v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; - - FOR referrer_key IN referrer_keys LOOP - a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' || -referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type || -'''; END IF;'; - END LOOP; - - a_output := a_output || ' RETURN NULL; END;'; - EXECUTE IMMEDIATE a_output; -END; -/ -show errors - - - - - Here is how this function would end up in PostgreSQL: - - -CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' -DECLARE - referrer_keys RECORD; -- Declare a generic record to be used in a FOR - a_output varchar(4000); -BEGIN - a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR) - RETURNS VARCHAR AS '''' - DECLARE - v_host ALIAS FOR $1; - v_domain ALIAS FOR $2; - v_url ALIAS FOR $3; - BEGIN ''; - - -- - -- Notice how we scan through the results of a query in a FOR loop - -- using the FOR <record> construct. - -- - - FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP - a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' - || referrer_keys.key_string || '''''''''' THEN RETURN '''''' - || referrer_keys.referrer_type || ''''''; END IF;''; - END LOOP; - - a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';''; - - -- This works because we are not substituting any variables - -- Otherwise it would fail. Look at PERFORM for another way to run functions - - EXECUTE a_output; -END; -' LANGUAGE 'plpgsql'; - - - - - - - A Procedure with a lot of String Manipulation and OUT Parameters - - - - The following Oracle PL/SQL procedure is used to parse a URL and - return several elements (host, path and query). It is an - procedure because in PL/pgSQL functions only one value can be returned - (see ). In - PostgreSQL, one way to work around this is to split the procedure - in three different functions: one to return the host, another for - the path and another for the query. - - - -CREATE OR REPLACE PROCEDURE cs_parse_url( - v_url IN VARCHAR, - v_host OUT VARCHAR, -- This will be passed back - v_path OUT VARCHAR, -- This one too - v_query OUT VARCHAR) -- And this one -is - a_pos1 INTEGER; - a_pos2 INTEGER; -begin - v_host := NULL; - v_path := NULL; - v_query := NULL; - a_pos1 := instr(v_url, '//'); -- PostgreSQL doesn't have an instr function - - IF a_pos1 = 0 THEN - RETURN; - END IF; - a_pos2 := instr(v_url, '/', a_pos1 + 2); - IF a_pos2 = 0 THEN - v_host := substr(v_url, a_pos1 + 2); - v_path := '/'; - RETURN; - END IF; - - v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); - a_pos1 := instr(v_url, '?', a_pos2 + 1); - - IF a_pos1 = 0 THEN - v_path := substr(v_url, a_pos2); - RETURN; - END IF; - - v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); - v_query := substr(v_url, a_pos1 + 1); -END; -/ -show errors; - - - - Here is how this procedure could be translated for PostgreSQL: - - -CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS ' -DECLARE - v_url ALIAS FOR $1; - v_host VARCHAR; - v_path VARCHAR; - a_pos1 INTEGER; - a_pos2 INTEGER; - a_pos3 INTEGER; -BEGIN - v_host := NULL; - a_pos1 := instr(v_url,''//''); - - IF a_pos1 = 0 THEN - RETURN ''''; -- Return a blank - END IF; - - a_pos2 := instr(v_url,''/'',a_pos1 + 2); - IF a_pos2 = 0 THEN - v_host := substr(v_url, a_pos1 + 2); - v_path := ''/''; - RETURN v_host; - END IF; - - v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); - RETURN v_host; -END; -' LANGUAGE 'plpgsql'; - - - - - - - PostgreSQL does not have an instr function, - so you can work around it using a combination of other functions. - I got tired of doing this and created my own - instr functions that behave exactly like - Oracle's (it makes life easier). See the for the code. - - - - - - - Procedures - - - - Oracle procedures give a little more flexibility to the developer - because nothing needs to be explicitly returned, but it can be - through the use of INOUT or OUT parameters. - - - - An example: - - -CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS - a_running_job_count INTEGER; - PRAGMA AUTONOMOUS_TRANSACTION; -BEGIN - LOCK TABLE cs_jobs IN EXCLUSIVE MODE; - - SELECT count(*) INTO a_running_job_count - FROM cs_jobs - WHERE end_stamp IS NULL; - - IF a_running_job_count > 0 THEN - COMMIT; -- free lock - raise_application_error(-20000, 'Unable to create a new job: a job is currently running.'); - END IF; - - DELETE FROM cs_active_job; - INSERT INTO cs_active_job(job_id) VALUES (v_job_id); - - BEGIN - INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate); - EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists - END; - COMMIT; -END; -/ -show errors - - - - - Procedures like this can be easily converted into PostgreSQL - functions returning an INTEGER. This procedure in - particular is interesting because it can teach us some things: - - - - - There is no pragma statement in PostgreSQL. - - - - - - If you do a LOCK TABLE in PL/pgSQL, the lock - will not be released until the calling transaction is finished. - - - - - - You also cannot have transactions in PL/pgSQL procedures. The - entire function (and other functions called from therein) is - executed in a transaction and PostgreSQL rolls back the results if - something goes wrong. Therefore only one - BEGIN statement is allowed. - - - - - - The exception when would have to be replaced by an - IF statement. - - - - - - - So let's see one of the ways we could port this procedure to PL/pgSQL: - - -CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS ' -DECLARE - v_job_id ALIAS FOR $1; - a_running_job_count INTEGER; - a_num INTEGER; - -- PRAGMA AUTONOMOUS_TRANSACTION; -BEGIN - LOCK TABLE cs_jobs IN EXCLUSIVE MODE; - SELECT count(*) INTO a_running_job_count - FROM cs_jobs - WHERE end_stamp IS NULL; - - IF a_running_job_count > 0 - THEN - -- COMMIT; -- free lock - RAISE EXCEPTION ''Unable to create a new job: a job is currently running.''; - END IF; - - DELETE FROM cs_active_job; - INSERT INTO cs_active_job(job_id) VALUES (v_job_id); - - SELECT count(*) into a_num - FROM cs_jobs - WHERE job_id=v_job_id; - IF NOT FOUND THEN -- If nothing was returned in the last query - -- This job is not in the table so lets insert it. - INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, sysdate()); - RETURN 1; - ELSE - RAISE NOTICE ''Job already running.''; - END IF; - - RETURN 0; -END; -' LANGUAGE 'plpgsql'; - - - - - - Notice how you can raise notices (or errors) in PL/pgSQL. - - - - - - - - - Packages - - - - - I haven't done much with packages myself, so if there are - mistakes here, please let me know. - - - - - Packages are a way Oracle gives you to encapsulate PL/SQL - statements and functions into one entity, like Java classes, where - you define methods and objects. You can access these - objects/methods with a . - (dot). Here is an example of an Oracle package from ACS 4 (the - ArsDigita Community - System): - - -CREATE OR REPLACE PACKAGE BODY acs -AS - FUNCTION add_user ( - user_id IN users.user_id%TYPE DEFAULT NULL, - object_type IN acs_objects.object_type%TYPE DEFAULT 'user', - creation_date IN acs_objects.creation_date%TYPE DEFAULT sysdate, - creation_user IN acs_objects.creation_user%TYPE DEFAULT NULL, - creation_ip IN acs_objects.creation_ip%TYPE DEFAULT NULL, - ... - ) RETURN users.user_id%TYPE - IS - v_user_id users.user_id%TYPE; - v_rel_id membership_rels.rel_id%TYPE; - BEGIN - v_user_id := acs_user.new (user_id, object_type, creation_date, - creation_user, creation_ip, email, ... - RETURN v_user_id; - END; -END acs; -/ -show errors - - - - - We port this to PostgreSQL by creating the different objects of - the Oracle package as functions with a standard naming - convention. We have to pay attention to some other details, like - the lack of default parameters in PostgreSQL functions. The above - package would become something like this: - - -CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...) -RETURNS INTEGER AS ' -DECLARE - user_id ALIAS FOR $1; - object_type ALIAS FOR $2; - creation_date ALIAS FOR $3; - creation_user ALIAS FOR $4; - creation_ip ALIAS FOR $5; - ... - v_user_id users.user_id%TYPE; - v_rel_id membership_rels.rel_id%TYPE; -BEGIN - v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...); - ... - - RETURN v_user_id; -END; -' LANGUAGE 'plpgsql'; - - - - - - - Other Things to Watch For - - - - EXECUTE - - - The PostgreSQL version of EXECUTE works - nicely, but you have to remember to use - quote_literal(TEXT) and - quote_string(TEXT) as described in . Constructs of the type - EXECUTE ''SELECT * from $1''; will not work - unless you use these functions. - - - - - Optimizing <application>PL/pgSQL</application> Functions - - - PostgreSQL gives you two function creation modifiers to optimize - execution: iscachable (function always returns - the same result when given the same arguments) and - isstrict (function returns NULL if any - argument is NULL). Consult the CREATE - FUNCTION reference for details. - - - - To make use of these optimization attributes, you have to use the - WITH modifier in your CREATE - FUNCTION statement. Something like: - - -CREATE FUNCTION foo(...) RETURNS INTEGER AS ' -... -' LANGUAGE 'plpgsql' -WITH (isstrict, iscachable); - - - - - - - - Appendix - - - - - Code for my <function>instr</function> functions - - - - This function should probably be integrated into the core. - - - --- --- instr functions that mimic Oracle's counterpart --- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params. --- --- Searches string1 beginning at the nth character for the mth --- occurrence of string2. If n is negative, search backwards. If m is --- not passed, assume 1 (search starts at first character). --- --- by Roberto Mello (rmello@fslc.usu.edu) --- modified by Robert Gaszewski (graszew@poland.com) --- Licensed under the GPL v2 or later. --- - -CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS ' -DECLARE - pos integer; -BEGIN - pos:= instr($1,$2,1); - RETURN pos; -END; -' LANGUAGE 'plpgsql'; - - -CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS ' -DECLARE - string ALIAS FOR $1; - string_to_search ALIAS FOR $2; - beg_index ALIAS FOR $3; - pos integer NOT NULL DEFAULT 0; - temp_str VARCHAR; - beg INTEGER; - length INTEGER; - ss_length INTEGER; -BEGIN - IF beg_index > 0 THEN - - temp_str := substring(string FROM beg_index); - pos := position(string_to_search IN temp_str); - - IF pos = 0 THEN - RETURN 0; - ELSE - RETURN pos + beg_index - 1; - END IF; - ELSE - ss_length := char_length(string_to_search); - length := char_length(string); - beg := length + beg_index - ss_length + 2; - - WHILE beg > 0 LOOP - temp_str := substring(string FROM beg FOR ss_length); - pos := position(string_to_search IN temp_str); - - IF pos > 0 THEN - RETURN beg; - END IF; - - beg := beg - 1; - END LOOP; - RETURN 0; - END IF; -END; -' LANGUAGE 'plpgsql'; - --- --- Written by Robert Gaszewski (graszew@poland.com) --- Licensed under the GPL v2 or later. --- -CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS ' -DECLARE - string ALIAS FOR $1; - string_to_search ALIAS FOR $2; - beg_index ALIAS FOR $3; - occur_index ALIAS FOR $4; - pos integer NOT NULL DEFAULT 0; - occur_number INTEGER NOT NULL DEFAULT 0; - temp_str VARCHAR; - beg INTEGER; - i INTEGER; - length INTEGER; - ss_length INTEGER; -BEGIN - IF beg_index > 0 THEN - beg := beg_index; - temp_str := substring(string FROM beg_index); - - FOR i IN 1..occur_index LOOP - pos := position(string_to_search IN temp_str); - - IF i = 1 THEN - beg := beg + pos - 1; - ELSE - beg := beg + pos; - END IF; - - temp_str := substring(string FROM beg + 1); - END LOOP; - - IF pos = 0 THEN - RETURN 0; - ELSE - RETURN beg; - END IF; - ELSE - ss_length := char_length(string_to_search); - length := char_length(string); - beg := length + beg_index - ss_length + 2; - - WHILE beg > 0 LOOP - temp_str := substring(string FROM beg FOR ss_length); - pos := position(string_to_search IN temp_str); - - IF pos > 0 THEN - occur_number := occur_number + 1; - - IF occur_number = occur_index THEN - RETURN beg; - END IF; - END IF; - - beg := beg - 1; - END LOOP; - - RETURN 0; - END IF; -END; -' LANGUAGE 'plpgsql'; - - - - -
- -
- - -- cgit v1.2.3