Re: help needed for functions

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Nitin Ahire
Sent: Friday, September 16, 2005 7:21 PM
To: pgsql-general(at)postgresql(dot)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