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
