PostgreSQL - Red Hat Edition: Graphical Tools Guide
PrevChapter 3. Administrator ReferenceNext

3.12. Functions

In the object hierarchy under every schema are 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 Administrator to perform some of these tasks.

Figure 3-41. The CREATE FUNCTION Dialog

At the top of the CREATE FUNCTION 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 argtype parameter of 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 of 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 rettype parameter of CREATE FUNCTION. If the function returns a set of this return type, then check the Return a Set checkbox.

On PostgreSQL 7.3, the radio buttons for Volatility, On Null Input, and Security are enabled (with the defaults selected). Refer to the PostgreSQL 7.3 Reference Manual for more information on these options.

TipTip
 

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 this version of Administrator), you can enter the function definition directly into the Function Definition field. (Refer to the PL/pgSQL, PL/Tcl, and PL/Perl chapters of the PostgreSQL 7.3 Programmer's Guide for more information on the exact syntax governing function definitions in these 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 of 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 of CREATE FUNCTION).

If the function language is internal, then the function definition must be specified by entering it into the Internal Function Name. Refer to the CREATE FUNCTION section in the PostgreSQL 7.3 Reference Manual for more information on these parameters.

3.12.1. 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 takes a Customer's ID as input and returns the total cost of all items bought by that customer.

  1. A function is an object under a schema, so to create a function, first expand the database under which you wish to create the function (click the [O-] beside the database name), then expand the public schema.

  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, select numeric(7,2).

  7. Leave the Volatility, On Null Input, and Security in their default positions.

  8. For the Function Definition, type:

    SELECT sum(total_cost) FROM web_store_schema.invoices WHERE
    invoices.customer_id=web_store_schema.customers.customer_id;

    Figure 3-42. The CREATE FUNCTION Dialog for SQL

  9. 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.

3.12.2. Creating a Function Written in C

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

In the Section 3.15 Languages, there is an example of how to add the language plpgsql to a database. In those examples, the function plpgsql_call_handler() was 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 schema, so to drop a function, first expand the database from which you wish to drop the function (click the [O-] beside the database name), then expand the public schema and the Functions node. To drop the function, right-click on plpgsql_call_handler() and select DROP FUNCTION.

  2. To create the new function, expand the database and schema under which you wish to create the function (click the [O-] beside the database name), then expand the public schema.

  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: LANGUAGE_HANDLER

  7. Leave the Volatility, On Null Input, and Security in their default positions.

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

    /usr/lib/pgsql/plpgsql.so

  9. 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 3-43. The CREATE FUNCTION Dialog for C

  10. Click OK to define this function.

Figure 3-44. The Functions Tree View and Summary View

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

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

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 to save the new comment.

Dropping

To drop a function, right-click on it and select either DROP RESTRICT or DROP CASCADE. DROP RESTRICT will not drop the function if there are any dependent objects. DROP CASCADE automatically drops the function and all objects that depend on it.

If Preferences => Ask For Confirmations is enabled, you are asked to confirm that you want the function dropped (and all the dependent objects in case of a DROP CASCADE); if it is disabled, the function is dropped immediately (along with its dependent objects if DROP CASCADE was selected). This action cannot be undone.

Privileges

See Section 3.18 Privileges.

Replacing

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.

To learn more about functions, refer to the PostgreSQL 7.3 Reference Manual.

PrevHomeNext
DomainsUpGroups