Скачать в pdf «PostgreSQL»

Figure 18.8 shows a more complicated PL/PGSQL function. It accepts a text argument and returns the argument in uppercase, with a space between each character. The next SELECT uses this result to display a report heading. This function illustrates the use of variables and WHILE loops in PL/PGSQL.

Figure 18.9 shows an even more complicated PL/PGSQL function. It takes a state name as a parameter and finds the proper state code. Because state names are longer than state codes, they are often misspelled. This function deals with misspellings by performing lookups in several ways. First, it attempts to find an exact match. If that attempt fails, it searches for a unique state name that matches the first 2, 4, or 6 characters, up to the length of the supplied string. If a unique state is not found, the function returns an empty string ( ”}, Figure 18.10 shows several getstateeadeO function calls. Thegetstateeade() function illustrates three new PL/PGSQL features:

%TYPE Data type that matches a database column.

record Data type that stores the result of a SELECT.

found A predefined BOOLEAN variable that represents the status of the previous SELECT INTO.

Many other PL/PGSQL features are covered in the User’s Manual mentioned in Appendix A.2.

Figure 18.11 shows a PL/PGSQL function that provides a server-side interface for maintaining the statename table. The function ehange_statename performs insert, update, and delete operations on the statename table. The function is called with a state code and state name. If the state code is not in the table, it is inserted. If it already exists, the state name is updated. If the function is called with an empty state name (» ), the state is deleted from the table. The function returns true (’t’) if statename was changed, and false (’f) if statename was unmodified. Figure 18.12 shows examples of its use.

Скачать в pdf «PostgreSQL»