Re: help needed for functions
От | Dinesh Pandey |
---|---|
Тема | Re: help needed for functions |
Дата | |
Msg-id | 20050916135820.012A9D83CC@svr1.postgresql.org обсуждение исходный текст |
Ответ на | help needed for functions (Nitin Ahire <nitin_eluminous@yahoo.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: