Skip site navigation (1) Skip section navigation (2)

Re: New from mssql. Help please

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: hendro <hendro(at)connexiasolutions(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: New from mssql. Help please
Date: 2004-05-22 19:27:25
Message-ID: 20040522192725.GB11098@campbell-lange.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Hendro

On 21/05/04, hendro (hendro(at)connexiasolutions(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>

In response to

pgsql-novice by date

Next:From: Robert MorganDate: 2004-05-23 21:47:17
Subject: sequence
Previous:From: hendroDate: 2004-05-22 10:29:10
Subject: Re: New from mssql. Help please

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group