Red Hat Database: Graphical Tools Guide
PrevChapter 4. RHDB Administrator ReferenceNext

Functions

In the object hierarchy under any database, you will find Functions. You can add functions by right-clicking Functions and selecting CREATE FUNCTION. Once a function has been created, it can be commented on, replaced, or dropped. In this section, you will use RHDB Administrator to perform some of these tasks.

Figure 4-24. The CREATE FUNCTION Dialog

At the top of the dialog is an entry box for entering the name of the new function. This corresponds to the name parameter for the SQL command CREATE FUNCTION.

Next is an entry for the Argument List, which corresponds to the ftype parameter to CREATE FUNCTION. This is where the data types of the function's arguments are listed, separated by commas.

From the drop-down list for the Language you must select the language of the function; this corresponds to the langname parameter to CREATE FUNCTION. Only languages that have been installed in the database can be chosen.

The drop-down list for the Return Type lists the return types that can be chosen for this function; this corresponds to the "rtype" parameter to CREATE FUNCTION. If the function returns a set of this return type, then check the Return a Set checkbox.

The checkboxes for Cachable and Strict correspond to the items iscachable and isstrict, which may appear with the attribute parameter to CREATE FUNCTION.

Note

How the Function Definition is entered varies depending on the language chosen for the function. For functions written in SQL, or any of the supported languages (plperl, plpgsql, pltcl, and pltclu in RHDB Administrator Version 1.0), the function definition can be entered directly into the entry box for the Function Definition. (Refer to the PL/pgSQL, PL/Tcl, and PL/Perl chapters of the Red Hat Database Programmer's Guide for more information on the exact syntax governing function definitions in these different languages.)

The other option for entering function definitions in these languages is to import the definition from a file; you do this with the Import From File button. Once a definition has been imported from a file, the filename is shown in the File Imported From textbox. For functions written in C, the definition cannot be entered directly into the Function Definition entry box, nor can it be imported by using the Import From File button.

Similarly, the Export To File button exports a function definition from the Function Definition entry box to a file.

Functions written in C must have their definitions specified by entering the name of the file containing the dynamically loadable object (corresponding to the obj_file parameter to CREATE FUNCTION) in the entry box marked Shared Library.

If the exact C function name in this file is known, it can be entered in the Object Function Name (Optional) entry box (corresponding to the link_symbol parameter to CREATE FUNCTION).

If the function language is internal, then the function definition must be specified by entering into the Internal Function Name. Refer to the CREATE FUNCTION section in the Red Hat Database SQL Guide and Reference for more information on these parameters.

Creating a Function Written in SQL

In this section you will use the CREATE FUNCTION dialog to create a function called get_amount_by_customer. This new function will take a Customer's ID as input and return the total cost of all items bought by that customer.

  1. A function is an object under a database, so to create a function, first expand the database under which you wish to create the function (click the + beside the database name).

  2. Right-click on Functions and select CREATE FUNCTION. The CREATE FUNCTION dialog appears.

  3. The first field is the name of the function. Type: get_amount_by_customer

  4. In the Arguments List, enter: integer

    This is for the customer ID of the customer.

  5. From the Language drop-down list, select sql.

  6. As the Return Type, type: numeric(7,2) and as an Attribute check Strict.

  7. For the Function Definition, type: 
    SELECT sum(total_cost) FROM invoices WHERE
    invoices.customer_id=customers.customer_id;

    Figure 4-25. The CREATE FUNCTION Dialog for SQL

  8. Click OK to define this function.

Note that the function get_amount_by_customer(int4) has been added under the Functions node in the Tree View.

Creating a Function Written in C

In this example, you will create a function in C called plpgsql_call_handler().

In the the Section called Languages section, there is an example of how to add the language plpgsql to a database. In those examples, the function plpgsql_call_handler() was for the most part implicitly defined in our database. Here, you will create this function manually.

  1. First, drop the function plpgsql_call_handler() (if it exists).

    A function is an object under a database, so to drop a function, first expand the database from which you wish to drop the function (click the + beside the database name). To drop the function, right-click on plpgsql_call_handler() and select DROP FUNCTION.

  2. To create the new function, expand the database under which you wish to create the function (click the + beside the database name).

  3. Right-click on Functions and select CREATE FUNCTION. The CREATE FUNCTION dialog appears.

  4. For the name of the new function type: plpgsql_call_handler

  5. From the Language drop-down list, select C.

  6. As the Return Type, type: opaque

  7. For the Function Definition, enter the shared library path for this C function:

    /usr/lib/pgsql/plpgsql.so

  8. If you know the exact name of the C function in this file that implements the function you are defining, enter it as the Object Function Name. In this example, it is: plpgsql_call_handler

    Figure 4-26. The CREATE FUNCTION Dialog for C

  9. Click OK to define this function.

Figure 4-27. The Functions Tree View and Summary View

Note that the functions have been added under the Functions node in the Tree View.

RHDB Administrator also enables you to perform the following operations on existing functions:

Dropping

To drop a function, right-click on it and select DROP FUNCTION. If View -> Ask For Confirmations is enabled, you are asked to confirm that you want the function dropped; if it is disabled, the function is dropped immediately. This action cannot be undone.

Replacing

RHDB Administrator enables you to redefine a function's language, definition, and attributes. To do this, right-click on it and select REPLACE FUNCTION. This dialog is similar to the CREATE FUNCTION dialog.

If you do not want to lose the original definition of the function, you can save the original definition by clicking on the Export To File button. This brings up the Export To File dialog. In the Selection box, type the complete path of the file name you want to save the original definition to, then click OK.

To retrieve that original function definition, click the Import From File button.

Commenting

PostgreSQL allows commenting on functions, which makes them easier to identify. To comment on a function, right-click on it and select COMMENT. A dialog asks for the comment. If there is already a comment on the function, this comment is displayed. Type the new comment in the edit box and click OK (or press [Enter]) to save the new comment.

Refer to the Red Hat Database SQL Guide and Reference for more information on functions.

PrevHomeNext
Data TypesUpGroups