Re: New from mssql. Help please

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>