From: Andreas Tille <tillea@rki.de>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Scaler forms as function arguments
Date: Thu, 27 Nov 2003 09:42:09 +0100 (CET)
Message-ID: <Pine.LNX.4.56.0311270937220.12965@wr-linux02.rki.ivbb.bund.de> (raw)
In-Reply-To: <200311261725.21975.dev@archonet.com>
References: <Pine.LNX.4.56.0311261255210.17774@wr-linux02.rki.ivbb.bund.de>
<200311261725.21975.dev@archonet.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tillea@rki.de
Subject: Re: Scaler forms as function arguments
In-Reply-To: <Pine.LNX.4.56.0311270937220.12965@wr-linux02.rki.ivbb.bund.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox