Thread: Newbie question: How to CREATE FUNCTION

Hi,

I am new to PostgreSQL. So, please forgive my ignorane.

Can anyone tell me how to create a FUNCTION in PostgreSQL that performs the
same duty as the following MsSQL stored procedure?


*********************************************************
Create PROCEDURE AddEmployee   @Name                         NVARCHAR(255)   ,@Title                    NVARCHAR(255)
,@strErr                   VARCHAR(255)     OUTPUT
 

AS
BEGIN
   /* ~~~  Add the employee into the Employees table ~~~ */   INSERT INTO     Employees        (     name
,title       )   VALUES        (     @Name             ,@Title        )
 

   /* ~~~  Error checking ~~~ */   IF( @@rowcount = 0 )   BEGIN        SELECT @strErr = 'Errors adding the new
employee.\n'       RETURN -1   END   / * ~~
 
   /* ~~~  Return the auto id through the OUTPUT parameter ~~~ */   SELECT @strErr = CONVERT(VARCHAR(255), @@IDENTITY)
RETURN 0
 

END
*********************************************************


Can you also tell me how to add a new language (if necessary) like pgsql
into PostgreSQL? Pointers to learning pgsql will also be appreciated.

Thanks a lot in advance!


Steven

* Steven Chau <stevenc@astri.org> [15.05.2003 05:09]:
> 
> Hi,
> 
> I am new to PostgreSQL. So, please forgive my ignorane.
> 
> Can anyone tell me how to create a FUNCTION in PostgreSQL that performs the
> same duty as the following MsSQL stored procedure?

Go to

www.postgresql.org/docs/

and select interactive documentation for your version of Postgres.

SQL commands are described in Reference Manual.
Procedural languages and Server-side programming - in Programmer's manual.

Also, take a glance at `createlang' in section `Client applications' of
Reference manual.

-- 

Victor Yegorov

Steven Chau wrote:
> Hi,
> 
> I am new to PostgreSQL. So, please forgive my ignorane.
> 
> Can anyone tell me how to create a FUNCTION in PostgreSQL that performs the
> same duty as the following MsSQL stored procedure?

Here it is without any commentary. I'll leave the explanation to some 
quality time spent with the fine manual. Here are a couple of starting 
points:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/functions-sequence.
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html

-- create a sequnce and the employees table
create sequence public.employees_id_seq;
create table public.employees( id integer not null    default nextval('public.employees_id_seq')    primary key, name
text,title text
 
);

-- create the addemployee function in plpgsql
CREATE OR REPLACE FUNCTION addemployee(text, text)
RETURNS integer AS '
DECLARE v_name alias for $1; v_title alias for $2; v_rowcount integer; v_id integer;
BEGIN -- Add the employee into the Employees table INSERT INTO employees (name, title) VALUES (v_name, v_title);
 GET DIAGNOSTICS v_rowcount = ROW_COUNT; IF v_rowcount != 1 THEN  -- Error  v_id := -1; ELSE  -- Otherwise return the
idthrough  SELECT into v_id currval(''public.employees_id_seq''); END IF;
 
 RETURN v_id;
END;
' LANGUAGE 'plpgsql';

regression=# SELECT addemployee('John Doe', 'DBA'); addemployee
-------------           1
(1 row)

regression=# SELECT addemployee('Jane Foo', 'Senior DBA'); addemployee
-------------           2
(1 row)

regression=# SELECT * FROM employees; id |   name   |   title
----+----------+------------  1 | John Doe | DBA  2 | Jane Foo | Senior DBA
(2 rows)


> Can you also tell me how to add a new language (if necessary) like pgsql
> into PostgreSQL? Pointers to learning pgsql will also be appreciated.
> 

http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xplang-install.html

HTH,

Joe