![]() This substitutes for the terminating / in the Oracle approach. In PostgreSQL, the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it. Also, IS becomes AS, and you need to add a LANGUAGE clause because PL/pgSQL is not the only possible function language. The RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL. In the examples in this section, we'll use varchar, but text is often a better choice if you do not need specific string length limits. The type name varchar2 has to be changed to varchar or text. There are various notational differences for the use of cursor variables. An advantage of this is that the variable values are still accessible after the loop exits. ![]() (See Section 43.6.5.5.)įOR loops over queries (other than cursors) also work differently: the target variable(s) must have been declared, whereas PL/SQL always declares them implicitly. This incompatibility is unfortunate but is unlikely to be changed. Integer FOR loops with REVERSE work differently: PL/SQL counts down from the second number to the first, while PL/pgSQL counts down from the first number to the second, requiring the loop bounds to be swapped when porting. You can keep per-session state in temporary tables instead. Since there are no packages, there are no package-level variables either. Instead of packages, use schemas to organize your functions into groups. Similarly, replace type number with numeric, or use some other numeric data type if there's a more appropriate one. In PostgreSQL, use type varchar or text instead. ![]() For example, in Oracle string values are commonly declared as being of type varchar2, which is a non-SQL-standard type. (See Section 43.12.1.)ĭata type names often need translation. Therefore you need to use dollar quoting or escape single quotes in the function body. In PostgreSQL the function body must be written as a string literal. It's often best to avoid such ambiguities in the first place, but if you have to port a large amount of code that depends on this behavior, setting variable_conflict may be the best solution. You can specify plpgsql.variable_conflict = use_column to change this behavior to match PL/SQL, as explained in Section 43.11.1. By default, PL/pgSQL will throw an error complaining that the name is ambiguous. If a name used in an SQL command could be either a column name of a table used in the command or a reference to a variable of the function, PL/SQL treats it as a column name. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |