Message-ID: <3EC30583.8040608@joeconway.com>
Date: Wed, 14 May 2003 20:12:03 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Steven Chau <stevenc@astri.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Newbie question: How to CREATE FUNCTION
References: <00b501c31a86$6a6a3130$4706050a@stevenc>
In-Reply-To: <00b501c31a86$6a6a3130$4706050a@stevenc>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 id through
   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