Re: New from mssql. Help please

Hi Hendro

On 21/05/04, hendro (hendro@connexiasolutions.com) wrote:
> I'm new here.
> My major problem is working with functions. (previously I use SQL
> Server)
> For instance, I haven't find a way to alter a function, or to view
> function list.

For general help, start with \? on the psql command line.

For a function list, do \df on the psql command line.

This also works with wildcards, so you can get a list of functions
matching a pattern, eg:

    st=> \df *sum*
                           List of functions
     Result data type |   Schema   |   Name   | Argument data types
    ------------------+------------+----------+---------------------
     bigint           | pg_catalog | int2_sum | bigint, smallint
     bigint           | pg_catalog | int4_sum | bigint, integer
     numeric          | pg_catalog | int8_sum | numeric, bigint
    (3 rows)

> (any keystroke like \d that will display functions ?)
> Can anybody suggest a nice place to start? An URL perhaps?

If you are writing your own functions, you can use the following syntax
to create or replace functions:

    CREATE OR REPLACE FUNCTION
        fn_c3_delete_contact (integer, varchar) RETURNS INTEGER
    AS '
    DECLARE
        creator       ALIAS for $1;
        recone        RECORD;
    BEGIN

        IF creator IS NULL
        THEN
            RAISE EXCEPTION ''no creator found at fn_c3_delete_contact'';
        END IF;

        RETURN 1;

    END;'
        LANGUAGE plpgsql;

A good way of upgrading a set of functions is to have a file with the
names of all the files in which you have functions, eg:

...
\i sql/fn_m3_explore.sql
\i sql/fn_p1_create_page.sql
\i sql/fn_p2_edit_page.sql
\i sql/fn_p3_move_page.sql
...

Now reference the file containting these references on the psql command
line (eg \i sql/fn_function_loader.sql) and all of your functions will
be updated.

Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>