pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNewbie question: How to CREATE FUNCTION
3+ messages / 3 participants
[nested] [flat]
* Newbie question: How to CREATE FUNCTION
@ 2003-05-15 02:04 Steven Chau <stevenc@astri.org>
2003-05-15 02:37 ` Re: Newbie question: How to CREATE FUNCTION Victor Yegorov <viy@nordlb.lv>
2003-05-15 03:12 ` Re: Newbie question: How to CREATE FUNCTION Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 3+ messages in thread
From: Steven Chau @ 2003-05-15 02:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Newbie question: How to CREATE FUNCTION
2003-05-15 02:04 Newbie question: How to CREATE FUNCTION Steven Chau <stevenc@astri.org>
@ 2003-05-15 02:37 ` Victor Yegorov <viy@nordlb.lv>
1 sibling, 0 replies; 3+ messages in thread
From: Victor Yegorov @ 2003-05-15 02:37 UTC (permalink / raw)
To: Steven Chau <stevenc@astri.org>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Newbie question: How to CREATE FUNCTION
2003-05-15 02:04 Newbie question: How to CREATE FUNCTION Steven Chau <stevenc@astri.org>
@ 2003-05-15 03:12 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2003-05-15 03:12 UTC (permalink / raw)
To: Steven Chau <stevenc@astri.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-15 03:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-15 02:04 Newbie question: How to CREATE FUNCTION Steven Chau <stevenc@astri.org>
2003-05-15 02:37 ` Victor Yegorov <viy@nordlb.lv>
2003-05-15 03:12 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox