pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Newbie 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>
  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:37  Victor Yegorov <viy@nordlb.lv>
  parent: Steven Chau <stevenc@astri.org>
  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 03:12  Joe Conway <mail@joeconway.com>
  parent: Steven Chau <stevenc@astri.org>
  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