Thread: help needed for functions

help needed for functions

From
Nitin Ahire
Date:
Hello All,
 
I am new to postgresql database.
I am transferring current database from mssql database to postgresql 7.4
 
I am facing problems for stored procedures. Is their any way so that I can transfer my existing stored procedure from mssql to postgre ?
 
I already read about functions & tried to implement it but I could not found any way to get multiple columns from a function.
Also I would like to get help about selecting values from two tables using function.
 
 
Thanks
Nitin

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: help needed for functions

From
"Dinesh Pandey"
Date:

Yes, you can use “SETOF” for multiple records.

 

See Examples

-------------------------------------------

 

CREATE TABLE department(id int primary key, name text);

CREATE TABLE employee(id int primary key, name text, salary int, departmentid int references department);

 

-----------------------------------------------------------------

 

INSERT INTO department values (1, 'Management');

INSERT INTO department values (2, 'IT');

 

INSERT INTO employee values (1, 'John Smith', 30000, 1);

INSERT INTO employee values (2, 'Jane Doe', 50000, 1);

INSERT INTO employee values (3, 'Jack Jackson', 60000, 2);

 

-----------------------------------------------------------------

 

CREATE OR REPLACE FUNCTION GetEmployees()

RETURNS SETOF employee

AS

          'select * from employee;'

LANGUAGE 'sql';

 

-----------------------------------------------------------------

 

CREATE TYPE HOLDER AS (departmentid int, totalsalary int8);

 

-----------------------------------------------------------------

 

CREATE or replace FUNCTION SqlDepartmentSalaries()

RETURNS SETOF holder

AS

'

    select departmentid, sum(salary) as totalsalary from GetEmployees() as a group by departmentid

'

LANGUAGE 'sql';

----------------------------

select * from SqlDepartmentSalaries();

-----------------------------------------------------------------

--We can do the same in PLPGSQL in this way.

 

CREATE OR REPLACE FUNCTION PLpgSQLDepartmentSalaries()

RETURNS SETOF holder

AS

'

DECLARE

    r holder%rowtype;

BEGIN

    FOR r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid

    LOOP

        return next r;

    END LOOP;

    RETURN;

END

'

LANGUAGE 'plpgsql';

 

-----------------------------------------------------------------

 

 

Thanks
Dinesh Pandey


From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Nitin Ahire
Sent: Friday, September 16, 2005 7:21 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] help needed for functions

 

Hello All,

 

I am new to postgresql database.

I am transferring current database from mssql database to postgresql 7.4

 

I am facing problems for stored procedures. Is their any way so that I can transfer my existing stored procedure from mssql to postgre ?

 

I already read about functions & tried to implement it but I could not found any way to get multiple columns from a function.

Also I would like to get help about selecting values from two tables using function.

 

 

Thanks

Nitin

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: help needed for functions

From
"A. Kretschmer"
Date:
am  16.09.2005, um  6:51:16 -0700 mailte Nitin Ahire folgendes:
> I am facing problems for stored procedures. Is their any way so that I
> can transfer my existing stored procedure from mssql to postgre ?

I guess: no.

>
> I already read about functions & tried to implement it but I could not
> found any way to get multiple columns from a function.  Also I would

http://techdocs.postgresql.org/guides/SetReturningFunctions
http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835

Read this links!


> like to get help about selecting values from two tables using
> function.

Where exactly is the problem?


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===