February 2001 Roberto Mello

rmello@fslc.usu.edu

This document explains differences between Oracle's PL/SQL and PostgreSQL's PL/pgSQL in the hopes of helping developers port applications from Oracle to PostgreSQL (e.g. ACS 4.x). Except for portions of this document quoted from other sources, this document is licensed under the GNU Free Documentation License (GFDL). Most of the code here is from the ArsDigita Clickstream module that I ported to PostgreSQL when I took an internship with OpenForce in the summer of 2000. PL/pgSQL is similar to PL/SQL in many aspects. It's 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's being improved. Some things you should keep in mind when porting from Oracle to PostgreSQL: No default parameters in PG You can overload functions in PG (can use to work around lack of default parameters) Assignments, loops and conditionals are similar. No need for cursors in PG, just put the query in the FOR statement (example below) In PostgreSQL you need to escape single quotes. See Quote Me on That. All examples here are for PG 7.1, where NULLs in functions are treated correctly and OUTER JOINs are implemented. 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). 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. So here's the scoop (you'll love this little chart): No. of Quotes Usage Example Result 1 To begin/terminate functions CREATE FUNCTION foo() RETURNS INTEGER AS '...' language 'plpgsql'; ditto 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). I guess you'd only need that if you were using a function to generate other functions (like in this nasty example). 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; Here's 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: The or replace statement is not allowed. You'll have to explicitly DROP the function before creating it to achieve similar results. PG does not accept named parameter. You'll 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 keyword becomes returns in PG. On PostgreSQL functions are created using single quotes as delimiters, so you have to escape single quotes inside your functions in order to use them (which can be quite annoying at times. See Quote Me on That). The /show errors does not exist in PG. So let's see how this function would be look like ported to PG: drop function cs_fmt_browser_version(varchar,varchar); create 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'; The following procedure grabs rows from a select statement and builds a giant function with the results for the sake of efficiency. Notice the differences in CURSORs, FOR loops, the need to escape single quotes in PG among other things. 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's how this function would end up in PG: 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; ''; -- -- Notice how we scan through the results of a query in a FOR loop -- using the FOR ROW construct. -- FOR ROW 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. EXECUTE a_output; end; ' language 'plpgsql'; 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 functions only one value can be returned (see Procedures). 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, '//'); -- PG has no 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's how this procedure could be translated into PG: drop function cs_parse_url_host(varchar); create 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'; Note: PostgreSQL has no 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 Appendix for the code. 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 PG functions returning an INTEGER. This procedure in particular is interesting because it can teach us some things: There's no pragma statement in PG. If you do a LOCK TABLE in PL/pgSQL, the lock will only be released when the function resumes. You also can't have transactions in PL/pgSQL procedures. The entire function (and other functions called from therein) is executed in a transaction and PG rolls back the results if something goes wrong. Therefore only one BEGIN statement is allowed. The exception when would have to be replaced with an IF statement. Notice how you can raise errors an notices in PL/pgSQL. So let's see one of the ways we could port this procedure to PL/pgSQL: drop function cs_create_job(integer); create 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'; Note: I haven't done much with packages myself, so if there are mistakes here, please let me know. Packages area way Oracle gives you to encapsulate PL/SQL statements and functions into one thing. Like Java classes, where you define methods and objects. You can access these objects/methods with a "." (dot). Here's 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 within an 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,datetime,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'; The PostgreSQL version of execute works nicely, but you have to remember some things: You should use quote_literal(TEXT) and quote_string(TEXT). According to the PostgreSQL Programmer's Guide "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 intact." Constructs of the type EXECUTE ''SELECT * from $1''; will not work unless you use the above functions. PostgreSQL gives you two function creation modifiers to optimize execution. From the PostgreSQL Reference: iscachable iscachable indicates that the function always returns the same result when given the same argument values (i.e., it does not do database lookups or otherwise use information not directly present in its parameter list). The optimizer uses iscachable to know whether it is safe to pre-evaluate a call of the function. isstrict isstrict indicates that the function always returns NULL whenever any of its arguments are NULL. If this attribute is specified, the function is not executed when there are NULL arguments; instead a NULL result is assumed automatically. When isstrict is not specified, the function will be called for NULL inputs. It is then the function author's responsibility to check for NULLs if necessary and respond appropriately. 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); The third function (that takes 4 parameters) is implemented in PL/Tcl but I plan on porting it to PL/pgSQL so in case we want to include it in OpenACS we don't need to require PL/Tcl. Plus PL/pgSQL should be more efficient. -- -- 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) -- Licensed under the GPL v2 or later. -- DROP FUNCTION instr(varchar,varchar); CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS ' DECLARE pos integer; BEGIN pos:= instr($1,$2,1); RETURN pos; END; ' language 'plpgsql'; DROP FUNCTION instr(varchar,varchar,integer); 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; ending integer; temp_str varchar; beg integer; length integer; temp_int integer; BEGIN IF beg_index > 0 THEN -- Get substring from 1 to beg_index 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 length := char_length(string); IF beg_index = -1 THEN ending := length; beg := ending; temp_int := 1; ELSE ending := length - abs(beg_index); beg := ending; temp_int := ending - beg; END IF; WHILE pos = 0 AND beg <> 1 LOOP temp_str := substring(string FROM beg FOR temp_int); pos := position(string_to_search IN temp_str); -- Keep moving left beg := beg - 1; temp_int := (ending - beg) + 1; END LOOP; END IF; IF pos = 0 THEN RETURN 0; ELSE RETURN beg + 1; END IF; END; ' language 'plpgsql'; -- -- The next one (where all four params are passed) is in PL/Tcl -- because I had no more patience to do it in PL/pgSQL. -- It'd probably be faster in PL/pgSQL (that being the reason why -- I implemented the first two functions in PL/pgSQL) so someday I'll do it. -- DROP FUNCTION instr(varchar,varchar,integer,integer); CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS ' set string1 $1 set string2 $2 set n $3 set m $4 if { $n > 0 } { set pos [string first $string2 $string1 [expr $n -1]] if { $pos < 0 } { return 0 } else { for { set i 1 } { $i < $m } { incr i } { set pos [string first $string2 $string1 [expr $pos + 1]] if { $pos < 0 } { return 0 } } } } if { $n < 0 } { set pos [string last $string2 $string1 [expr [string length $string1] + $n]] if { $pos < 0 } { return 0 } else { for { set i 1 } { $i < $m } { incr i } { # n is negative so we add set pos [string last $string2 $string1 [expr $pos - 1]] if { $pos < 0 } { return 0 } } } } if { $pos < 0 } { return 0 } else { return [expr $pos + 1] } ' language 'pltcl';