Function Creation and Execution

CREATE FUNCTION, of course, tells the database a function is being created. The function name, which used to call the function later, follows CREATE FUNCTION. In this case, that name is hello_world(). The RETURNS clause tells the database what type of data will be produced by the function. Our hello_world() function returns TEXT data.

Ads

The $$ symbol is PostgreSQL-specific and acts like double quotes, informing the database that everything between two $$ symbols should be treated as a single text string. BEGIN and END define the beginning and end of the operations the function should perform, and RETURN simply passes the ‘Hello, World!’ string out of the function when it finishes running. ‘Hello, World!’ is the TEXT data pre-referenced in the RETURNS TEXT clause on the CREATE FUNCTION line, and, after it is returned from the SELECT statement, it appears in the result set. Finally, LANGUAGE plpgsql is specified because this is a PL/pgSQL function. PostgreSQL requires the language to be explicitly mentioned, since PostgreSQL supports many different procedural languages.

Figure 2 shows the creation and execution of a similar hello_world() function in MySQL.

As you can see, the syntax and execution methods for the hello_world() functions written in PostgreSQL and MySQL are nearly the same.

In comparing these two examples, the most important thing to note is the DELIMITER in MySQL. This serves a similar function to the $$ symbol in PostgreSQL. The DELIMITER is the character that ends an SQL statement. In MySQL and most other SQL databases, it defaults to a semicolon (‘;’). However, when it is changed, in this case to //, MySQL interprets the semicolon as being simply another part of the FUNCTION statement, rather than the end of the function. The function is completed with the // symbol, and the DELIMITER is reset to the semicolon on the next line.

Some databases, like MySQL, can use the DETERMINISTIC adjective to qualify functions. This essentially means that, if a function is run many times with the same input values, it will always return the same values. Most basic functions are deterministic in nature.

Figure 3 shows the creation and execution of hello_world() in Oracle’s PL/SQL language.

Again, the Oracle example is similar to the PostgreSQL and MySQL examples. Three differences should be noted, however. First, the empty parentheses after hello_world in the CREATE FUNCTION line, as seen in the other examples, are not allowed in Oracle. Parentheses may only exist if one or more parameters are to be passed into the function.

Also, the / symbol at the end of the CREATE FUNCTION statement serves as an “end-of-file” marker, which informs Oracle that the function is complete. And, lastly, a SELECT instruction must always be executed FROM an object in Oracle. In this case, the dual table was chosen because it is the Oracle “dummy” table. However, any other table would have served the same purpose.

Ads

Beyond “Hello, World!”

While “Hello, World!” is the standard way to begin learning a programming language, there is much more to SQL programming than simply returning a string of text. Further examples and explanations of programming in SQL and other SQL-related procedural languages are given in other articles in this series.

First Page: SQL Programming