|
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 return next r; END RETURN; END ' LANGUAGE 'plpgsql'; ----------------------------------------------------------------- Thanks From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On
Behalf Of Nitin Ahire 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
__________________________________________________ |
