From 9b3caebb0aa38f5669bc1018de24535eafab67af Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 8 Aug 2004 00:50:58 +0000 Subject: [PATCH] Update plpgsql documentation for 8.0 (mostly, make use of named function parameters and dollar quoting in examples; do some polishing of the existing dollar-quoting docs). The 'how to port from Oracle' section is looking pretty respectable these days ... --- doc/src/sgml/plpgsql.sgml | 664 +++++++++++++++++++++----------------- 1 file changed, 360 insertions(+), 304 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index e44b886214..0615440725 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -49,6 +49,15 @@ $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.43 2004/07/31 23:04:54 tgl Exp + + 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. + For example, it is possible to + create complex conditional computation functions and later use + them to define operators or use them in index expressions. + + Overview @@ -136,15 +145,6 @@ $$ LANGUAGE plpgsql; - - 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. - For example, it is possible to - create complex conditional computation functions and later use - them to define operators or use them in index expressions. - - Advantages of Using <application>PL/pgSQL</application> @@ -230,10 +230,9 @@ $$ LANGUAGE plpgsql; REPLACE FUNCTION. That way you can just reload the file to update the function definition. For example: -CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS ' +CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$ .... -end; -' LANGUAGE plpgsql; +$$ LANGUAGE plpgsql; @@ -247,7 +246,7 @@ end; - Another good way to develop in PL/pgSQL is using a + Another good way to develop in PL/pgSQL is with a GUI database access tool that facilitates development in a procedural language. One example of such as a tool is PgAccess, although others exist. These tools often @@ -258,20 +257,36 @@ end; Handling of Quotation Marks - - Since the code of a PL/pgSQL function is specified in - CREATE FUNCTION as a string literal, single - quotes inside the function body must be escaped by doubling them - unless the string literal comprising the function body is dollar - quoted. - + + The code of a PL/pgSQL function is specified in + CREATE FUNCTION as a string literal. If you + write the string literal in the ordinary way with surrounding + single quotes, then any single quotes inside the function body + must be doubled; likewise any backslashes must be doubled. + Doubling quotes is at best tedious, and in more complicated cases + the code can become downright incomprehensible, because you can + easily find yourself needing half a dozen or more adjacent quote marks. + It's recommended that you instead write the function body as a + dollar-quoted string literal. In the dollar-quoting + approach, you never double any quote marks, but instead take care to + choose a different dollar-quoting delimiter for each level of + nesting you need. For example, you might write the CREATE + FUNCTION command as + +CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ + .... +$PROC$ LANGUAGE plpgsql; + + Within this, you might use quote marks for simple literal strings in + SQL commands and $$ to delimit fragments of SQL commands + that you are assembling as strings. If you need to quote text that + includes $$, you could use $Q$, and so on. + - - Doubling can lead to incomprehensible code at times, especially if - you are writing a function that generates other functions, as in the - example in . This - chart may be useful when translating pre-dollar quoting code into - something that is comprehensible. + + The following chart shows what you have to do when writing quote + marks without dollar quoting. It may be useful when translating + pre-dollar quoting code into something more comprehensible. @@ -281,11 +296,12 @@ end; To begin and end the function body, for example: -CREATE FUNCTION foo() RETURNS integer AS '...' - LANGUAGE plpgsql; +CREATE FUNCTION foo() RETURNS integer AS ' + .... +' LANGUAGE plpgsql; - Anywhere within the function body, quotation marks must - appear in pairs. + Anywhere within a single-quoted function body, quote marks + must appear in pairs. @@ -299,10 +315,13 @@ CREATE FUNCTION foo() RETURNS integer AS '...' a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar''; - The second line is seen by PL/pgSQL as + In the dollar-quoting approach, you'd just write +a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar'; + which is exactly what the PL/pgSQL parser would see + in either case. @@ -311,14 +330,22 @@ SELECT * FROM users WHERE f_name='foobar'; 4 quotation marks - When you need a single quotation mark in a string constant inside the function - body, for example: + When you need a single quotation mark in a string constant inside the + function body, for example: a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz'' The value actually appended to a_output would be: AND name LIKE 'foobar' AND xyz. + + In the dollar-quoting approach, you'd write + +a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$ + + being careful that any dollar-quote delimiters around this are not + just $$. + @@ -334,6 +361,12 @@ a_output := a_output || '' AND name LIKE ''''foobar'''''' The value appended to a_output would then be: AND name LIKE 'foobar'. + + In the dollar-quoting approach, this becomes + +a_output := a_output || $$ AND name LIKE 'foobar'$$ + + @@ -344,8 +377,9 @@ a_output := a_output || '' AND name LIKE ''''foobar'''''' When you want two single quotation marks in a string constant (which accounts for 8 quotation marks) and this is adjacent to the end of that string constant (2 more). You will probably only need that if - you are writing a function that generates other functions. For - example: + you are writing a function that generates other functions, as in + . + For example: a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' @@ -358,12 +392,23 @@ a_output := a_output || '' if v_'' || if v_... like ''...'' then return ''...''; end if; + + In the dollar-quoting approach, this becomes + +a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ + || referrer_keys.key_string || $$' + then return '$$ || referrer_keys.referrer_type + || $$'; end if;$$; + + where we assume we only need to put single quote marks into + a_output, because it will be re-quoted before use. + - A different approach is to escape quotation marks in the function body + A variant approach is to escape quotation marks in the function body with a backslash rather than by doubling them. With this method you'll find yourself writing things like \'\' instead of ''''. Some find this easier to keep track of, some @@ -402,12 +447,13 @@ END; - 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 */. + 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 */. @@ -446,13 +492,18 @@ $$ 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. + 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 that + transaction, since there would be no context for them to execute in. + However, a block containing an EXCEPTION clause effectively + forms a subtransaction that can be rolled back without affecting the + outer transaction. For more details see . @@ -462,9 +513,9 @@ $$ LANGUAGE plpgsql; All variables 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.) + (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.) @@ -522,10 +573,6 @@ user_id CONSTANT integer := 10; Aliases for Function Parameters - -name ALIAS FOR $n; - - Parameters passed to functions are named with the identifiers $1, $2, @@ -533,7 +580,25 @@ user_id CONSTANT integer := 10; $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: + There are two ways to create an alias. The preferred way is to give a + name to the parameter in the CREATE FUNCTION command, + for example: + +CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ +BEGIN + RETURN subtotal * 0.06; +END; +$$ LANGUAGE plpgsql; + + The other way, which was the only way available before + PostgreSQL 8.0, is to explicitly + declare an alias, using the declaration syntax + + +name ALIAS FOR $n; + + + The same example in this style looks like CREATE FUNCTION sales_tax(real) RETURNS real AS $$ DECLARE @@ -542,8 +607,9 @@ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql; - - + + Some more examples: + CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; @@ -554,9 +620,7 @@ END; $$ LANGUAGE plpgsql; -CREATE FUNCTION concat_selected_fields(tablename) RETURNS text AS $$ -DECLARE - in_t ALIAS FOR $1; +CREATE FUNCTION concat_selected_fields(in_t tablename) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; @@ -579,15 +643,12 @@ $$ LANGUAGE plpgsql; given an alias. For example, this function works on any data type that has a + operator: -CREATE FUNCTION add_three_values(anyelement, anyelement, anyelement) +CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; - first ALIAS FOR $1; - second ALIAS FOR $2; - third ALIAS FOR $3; BEGIN - result := first + second + third; + result := v1 + v2 + v3; RETURN result; END; $$ LANGUAGE plpgsql; @@ -681,17 +742,16 @@ user_id users.user_id%TYPE; Here is an example of using composite types: -CREATE FUNCTION use_two_tables(tablename) RETURNS text AS $$ +CREATE FUNCTION merge_fields(t_row tablename) RETURNS text AS $$ DECLARE - in_t ALIAS FOR $1; - use_t table2name%ROWTYPE; + t2_row table2name%ROWTYPE; BEGIN - SELECT * INTO use_t FROM table2name WHERE ... ; - RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7; + SELECT * INTO t2_row FROM table2name WHERE ... ; + RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql; -SELECT use_two_tables(t.*) FROM tablename t WHERE ... ; +SELECT merge_fields(t.*) FROM tablename t WHERE ... ; @@ -792,9 +852,7 @@ SELECT expression is a difference between what these two functions do: -CREATE FUNCTION logfunc1(text) RETURNS timestamp AS $$ - DECLARE - logtxt ALIAS FOR $1; +CREATE FUNCTION logfunc1(logtxt text) RETURNS timestamp AS $$ BEGIN INSERT INTO logtable VALUES (logtxt, 'now'); RETURN 'now'; @@ -805,9 +863,8 @@ $$ LANGUAGE plpgsql; and -CREATE FUNCTION logfunc2(text) RETURNS timestamp AS $$ +CREATE FUNCTION logfunc2(logtxt text) RETURNS timestamp AS $$ DECLARE - logtxt ALIAS FOR $1; curtime timestamp; BEGIN curtime := 'now'; @@ -1070,10 +1127,10 @@ EXECUTE command-string; - When working with dynamic commands you will have to face escaping - of single quotes in PL/pgSQL. The recommended method - is dollar quoting. If you have legacy code which does - not use dollar quoting, please refer to the + When working with dynamic commands you will often have to handle escaping + of single quotes. The recommended method for quoting fixed text in your + function body is dollar quoting. If you have legacy code which does + not use dollar quoting, please refer to the overview in , which can save you some effort when translating said code to a more reasonable scheme. @@ -1091,17 +1148,18 @@ EXECUTE command-string; The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. - There are two ways to extract a result from a dynamically-created - SELECT: one is to use the FOR-IN-EXECUTE + So there is no way to extract a result from a dynamically-created + SELECT using the plain EXECUTE + command. There are two other ways to do it, however: one is to use the + FOR-IN-EXECUTE loop form described in , and the other is to use a cursor with OPEN-FOR-EXECUTE, as described in . - An example (except where noted, all examples herein assume that - you are using dollar quoting): - + An example (this assumes that you are using dollar quoting, so the + quote marks need not be doubled): EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) @@ -1128,71 +1186,27 @@ EXECUTE 'UPDATE tbl SET ' - Here is a much larger example of a dynamic command and - EXECUTE: + Note that dollar quoting is only useful for quoting fixed text. + It would be a very bad idea to try to do the above example as -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;''; - - EXECUTE a_output; -END; -' LANGUAGE plpgsql; +EXECUTE 'UPDATE tbl SET ' + || quote_ident(colname) + || ' = $$' + || newvalue + || '$$ WHERE ...'; + because it would break if the contents of newvalue + happened to contain $$. The same objection would + apply to any other dollar-quoting delimiter you might pick. + So, to safely quote text that is not known in advance, you + must use quote_literal. + -And here is an equivalent using dollar quoting. At least it is more -legible than the above, although both versions show that the design, -rather than merely the formatting, needs to be re-thought. - - -CREATE or replace FUNCTION cs_update_referrer_type_proc2() RETURNS integer AS $func$ - 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 $innerfunc$ - 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; $innerfunc$ LANGUAGE plpgsql;'; - EXECUTE a_output; - RETURN -END; -$func$ LANGUAGE plpgsql; - + + A much larger example of a dynamic command and + EXECUTE can be seen in , which builds and executes a + CREATE FUNCTION command to define a new function. @@ -1200,9 +1214,9 @@ $func$ LANGUAGE plpgsql; Obtaining the Result Status - There are several ways to determine the effect of a command. The - first method is to use the GET DIAGNOSTICS - command, which has the form: + There are several ways to determine the effect of a command. The + first method is to use the GET DIAGNOSTICS + command, which has the form: GET DIAGNOSTICS variable = item , ... ; @@ -1337,7 +1351,7 @@ RETURN expression; If you have declared the function to return void, a RETURN statement - must still be specified; but in this case the expression following + must still be provided; but in this case the expression following RETURN is optional and will be ignored if present. @@ -1623,8 +1637,14 @@ EXIT label WHEN - If WHEN is present, loop exit occurs only if the specified condition - is true, otherwise control passes to the statement after EXIT. + If WHEN is present, loop exit occurs only if the specified + condition is true, otherwise control passes to the statement after + EXIT. + + + + EXIT can be used to cause early exit from all types of + loops; it is not limited to use with unconditional loops. @@ -1739,10 +1759,10 @@ FOR record_or_row IN query END LOOP; The record or row variable is successively assigned each row - resulting from the query (a SELECT command) and the loop - body is executed for each row. Here is an example: + resulting from the query (which must be a SELECT + command) and the loop body is executed for each row. Here is an example: -CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS ' +CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$ DECLARE mviews RECORD; BEGIN @@ -1752,15 +1772,15 @@ BEGIN -- 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); + 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; +$$ LANGUAGE plpgsql; If the loop is terminated by an EXIT statement, the last @@ -2507,8 +2527,8 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp This section explains differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language, - to help developers who port applications from Oracle to - PostgreSQL. + to help developers who port applications from + Oracle to PostgreSQL. @@ -2543,8 +2563,9 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp - In PostgreSQL you need to escape single - quotes in the function body. See . + In PostgreSQL you need to use dollar quoting or escape + single quotes in the function body. See . @@ -2571,7 +2592,8 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp Here is an Oracle PL/SQL function: -CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar) +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 @@ -2588,14 +2610,6 @@ 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 @@ -2604,7 +2618,7 @@ show errors; INOUT, for example, means that the parameter will receive a value and return another. PostgreSQL only has IN - parameters. + parameters, and hence there is no specification of the parameter kind. @@ -2614,21 +2628,26 @@ show errors; prototype (not the function body) becomes RETURNS in PostgreSQL. + Also, IS becomes AS, and you need to + add a LANGUAGE clause because PL/pgSQL + is not the only possible function language. - In PostgreSQL, functions are created using - single quotes as the delimiters of the function body, so you - have to escape single quotes inside the function body. + In PostgreSQL, the function body is considered + to be a string literal, so you need to use quote marks or dollar + quotes around it. This substitutes for the terminating / + in the Oracle approach. - The /show errors command does not exist in - PostgreSQL. + The show errors command does not exist in + PostgreSQL, and is not needed since errors are + reported automatically. @@ -2639,14 +2658,12 @@ show errors; PostgreSQL: -CREATE OR REPLACE FUNCTION cs_fmt_browser_version(varchar, varchar) +CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, + v_version 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; + RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; @@ -2657,7 +2674,7 @@ $$ LANGUAGE plpgsql; shows how to port a - function that creates another function and how to handle to + function that creates another function and how to handle the ensuing quoting problems. @@ -2669,7 +2686,7 @@ $$ LANGUAGE plpgsql; SELECT statement and builds a large function with the results in IF statements, for the sake of efficiency. Notice particularly the differences in the - cursor and the FOR loop, + cursor and the FOR loop. @@ -2680,19 +2697,22 @@ CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS SELECT * FROM cs_referrer_keys ORDER BY try_order; - a_output VARCHAR(4000); + func_cmd 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'; + func_cmd := '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;'; + func_cmd := func_cmd || + ' 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; + func_cmd := func_cmd || ' RETURN NULL; END;'; + + EXECUTE IMMEDIATE func_cmd; END; / show errors; @@ -2701,37 +2721,53 @@ show errors; Here is how this function would end up in PostgreSQL: - -CREATE or replace FUNCTION cs_update_referrer_type_proc() RETURNS -text AS $func$ +CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$ DECLARE - referrer_keys RECORD; -- declare a generic record to be used in a FOR - a_output TEXT; + referrer_key RECORD; -- declare a generic record to be used in a FOR + func_body text; + func_cmd text; BEGIN - a_output := 'CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) - RETURNS varchar AS $innerfunc$ - 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; $innerfunc$ LANGUAGE plpgsql;'; - - return a_output; -END; + func_body := 'BEGIN' ; + + -- Notice how we scan through the results of a query in a FOR loop + -- using the FOR <record> construct. + + FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP + func_body := func_body || + ' IF v_' || referrer_key.kind + || ' LIKE ' || quote_literal(referrer_key.key_string) + || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type) + || '; END IF;' ; + END LOOP; + + func_body := func_body || ' RETURN NULL; END;'; + + func_cmd := + 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar, + v_domain varchar, + v_url varchar) + RETURNS varchar AS ' + || quote_literal(func_body) + || ' LANGUAGE plpgsql;' ; + + EXECUTE func_cmd; + RETURN; +END; $func$ LANGUAGE plpgsql; + Notice how the body of the function is built separately and passed + through quote_literal to double any quote marks in it. This + technique is needed because we cannot safely use dollar quoting for + defining the new function: we do not know for sure what strings will + be interpolated from the referrer_key.key_string field. + (We are assuming here that referrer_key.kind can be + trusted to always be host, domain, or + url, but referrer_key.key_string might be + anything, in particular it might contain dollar signs.) This function + is actually an improvement on the Oracle original, because it will + not generate broken code when referrer_key.key_string or + referrer_key.referrer_type contain quote marks. @@ -2754,12 +2790,11 @@ $func$ LANGUAGE plpgsql; PL/pgSQL - The following Oracle PL/SQL procedure is used to parse a URL and - return several elements (host, path, and query). - PL/pgSQL functions can return only one value. 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. + The following Oracle PL/SQL procedure is used + to parse a URL and return several elements (host, path, and query). + In PostgreSQL, functions can return only one value. + One way to work around this is to make the return value a composite + type (row type). @@ -2806,37 +2841,54 @@ show errors; - Here is how the PL/pgSQL function that returns - the host part could look like: - + Here is a possible translation into PL/pgSQL: -CREATE OR REPLACE FUNCTION cs_parse_url_host(varchar) RETURNS varchar AS $$ +CREATE TYPE cs_parse_url_result AS ( + v_host VARCHAR, + v_path VARCHAR, + v_query VARCHAR +); + +CREATE OR REPLACE FUNCTION cs_parse_url(v_url VARCHAR) +RETURNS cs_parse_url_result 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; + res cs_parse_url_result; + a_pos1 INTEGER; + a_pos2 INTEGER; +BEGIN + res.v_host := NULL; + res.v_path := NULL; + res.v_query := NULL; a_pos1 := instr(v_url, '//'); - IF a_pos1 = 0 THEN - RETURN ''; -- Return a blank - END IF; + IF a_pos1 = 0 THEN + RETURN res; + END IF; + a_pos2 := instr(v_url, '/', a_pos1 + 2); + IF a_pos2 = 0 THEN + res.v_host := substr(v_url, a_pos1 + 2); + res.v_path := '/'; + RETURN res; + 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; + res.v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); + a_pos1 := instr(v_url, '?', a_pos2 + 1); - v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); - RETURN v_host; -END; + IF a_pos1 = 0 THEN + res.v_path := substr(v_url, a_pos2); + RETURN res; + END IF; + + res.v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); + res.v_query := substr(v_url, a_pos1 + 1); + RETURN res; +END; $$ LANGUAGE plpgsql; + + + This function could be used like this: + +SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz'); @@ -2871,7 +2923,8 @@ BEGIN 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 + EXCEPTION + WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists END; COMMIT; END; @@ -2881,8 +2934,8 @@ show errors - Procedures like this can be easily converted into PostgreSQL - functions returning an integer. This procedure in + Procedures like this can easily be converted into PostgreSQL + functions returning void. This procedure in particular is interesting because it can teach us some things: @@ -2894,24 +2947,21 @@ show errors - If you do a LOCK TABLE in PL/pgSQL, the lock - will not be released until the calling transaction is finished. + 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 functions. The - entire function (and other functions called from therein) is - executed in one transaction and PostgreSQL rolls back the transaction if - something goes wrong. - - - - - - The exception when would have to be replaced by an - IF statement. + You cannot issue COMMIT in a + PL/pgSQL function. The function is + running within some outer transaction and so COMMIT + would imply terminating the function's execution. However, in + this particular case it is not necessary anyway, because the lock + obtained by the LOCK TABLE will be released when + we raise an error. @@ -2921,33 +2971,29 @@ show errors This is how we could port this procedure to PL/pgSQL: -CREATE OR REPLACE FUNCTION cs_create_job(integer) RETURNS integer AS $$ +CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$ DECLARE - v_job_id ALIAS FOR $1; a_running_job_count integer; - a_num integer; 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 - RAISE EXCEPTION 'Unable to create a new job: a job is currently running.'; + IF a_running_job_count > 0 THEN + 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, current_timestamp); - RETURN 1; - ELSE - RAISE NOTICE 'Job already running.'; - END IF; + BEGIN + INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now()); + EXCEPTION + WHEN unique_violation THEN + -- don't worry if it already exists + END; - RETURN 0; + RETURN; END; $$ LANGUAGE plpgsql; @@ -2955,10 +3001,24 @@ $$ LANGUAGE plpgsql; - Notice how you can raise notices (or errors) in PL/pgSQL. + The syntax of RAISE is considerably different from + Oracle's similar statement. + + + + + The exception names supported by PL/pgSQL are + different from Oracle's. The set of built-in exception names + is much larger (see ). + + The main functional difference between this procedure and the + Oracle equivalent is that the exclusive lock on the cs_jobs + table will be held until the calling transaction completes. Also, if + the caller later aborts (for example due to an error), the effects of + this procedure will be rolled back. @@ -2992,16 +3052,16 @@ $$ LANGUAGE plpgsql; PostgreSQL gives you two function creation - modifiers to optimize execution: the volatility (whether the + modifiers to optimize execution: volatility (whether the function always returns the same result when given the same - arguments) and the strictness (whether the - function returns null if any argument is null). Consult the description of - CREATE FUNCTION for details. + arguments) and strictness (whether the + function returns null if any argument is null). Consult the + reference page for details. - To make use of these optimization attributes, your - CREATE FUNCTION statement could look something + When making use of these optimization attributes, your + CREATE FUNCTION statement might look something like this: @@ -3017,8 +3077,8 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE; Appendix - This section contains the code for an Oracle-compatible - instr function that you can use to simplify + This section contains the code for a set of Oracle-compatible + instr functions that you can use to simplify your porting efforts. @@ -3039,14 +3099,12 @@ BEGIN pos:= instr($1, $2, 1); RETURN pos; END; -$$ LANGUAGE plpgsql; +$$ LANGUAGE plpgsql STRICT IMMUTABLE; -CREATE FUNCTION instr(varchar, varchar, integer) RETURNS integer AS $$ +CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index 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; @@ -3081,15 +3139,13 @@ BEGIN RETURN 0; END IF; END; -$$ LANGUAGE plpgsql; +$$ LANGUAGE plpgsql STRICT IMMUTABLE; -CREATE FUNCTION instr(varchar, varchar, integer, integer) RETURNS integer AS $$ +CREATE FUNCTION instr(string varchar, string_to_search varchar, + beg_index integer, occur_index 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; @@ -3142,7 +3198,7 @@ BEGIN RETURN 0; END IF; END; -$$ LANGUAGE plpgsql; +$$ LANGUAGE plpgsql STRICT IMMUTABLE; -- 2.40.0