Re: [SQL] Scaler forms as function arguments

[Andreas Tille]

On Wed, 26 Nov 2003, Richard Huxton wrote:

> Not as you've done it. You could pass in text "(1,2,3)", build your query and
> use EXECUTE to execute it.
This boils down the question to the problem which occured with
your promissing link below, because I need to use PL/pgSQL, right?

> Alternatively, you might be able to do it with an
> array parameter (sorry, I don't use arrays, so I can't be sure).
I'll give that a try.

> Read the section on plpgsql in the manuals, you return results one at a time.
> For some examples, see http://techdocs.postgresql.org/ and look for the "Set
> Returning Functions" item.
A very interesting article but if I try the example code:

   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 function GetEmployees() returns setof employee as 'select * from employee;' 
language 'sql';

   create type holder as (departmentid int, totalsalary int8);

   create function SqlDepartmentSalaries() returns setof holder as
   '
       select departmentid, sum(salary) as totalsalary from GetEmployees() group by 
departmentid
   '
   language 'sql';

   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';

I get:

test=# select PLpgSQLDepartmentSalaries() ;
WARNING:  Error occurred while executing PL/pgSQL function plpgsqldepartmentsalaries
WARNING:  line 5 at return next
ERROR:  Set-valued function called in context that cannot accept a set
test=#

Any hint what might be wrong here?  I'm using PostgreSQL 7.3.2 under Debian
GNU/Linux (testing).

Kind regards

          Andreas.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings