pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedScaler forms as function arguments
8+ messages / 4 participants
[nested] [flat]
* Scaler forms as function arguments
@ 2003-11-26 15:40 Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Tille @ 2003-11-26 15:40 UTC (permalink / raw)
To: pgsql-sql
Hi,
I want to write a function of the following type
CREATE FUNCTION test ( <scalar form type> )
RETURNS setof MyTable
AS
'SELECT * FROM MyTable WHERE id IN $1'
LANGUAGE 'SQL' ;
I was not able to find a reference whether this is possible and if yes
how to specify the argument type and how to call this function to hand over
the list for the IN clause correctly.
Next question. Is there any example how to return a SETOF MyTable in
a plpgsql function? I tried
CREATE FUNCTION test2()
RETURNS setof MyTable
AS '
DECLARE
result SETOF MyTable ;
BEGIN
result := (SELECT * FROM MyTable);
RETURN result ;
END; ' LANGUAGE 'plpgsql';
wich failed.
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
@ 2003-11-26 17:25 ` Richard Huxton <dev@archonet.com>
2003-11-27 08:42 ` Re: Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-28 05:47 ` Re: Scaler forms as function arguments Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 8+ messages in thread
From: Richard Huxton @ 2003-11-26 17:25 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; pgsql-sql
On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
> Hi,
>
> I want to write a function of the following type
>
> CREATE FUNCTION test ( <scalar form type> )
> RETURNS setof MyTable
> AS
> 'SELECT * FROM MyTable WHERE id IN $1'
> LANGUAGE 'SQL' ;
Not as you've done it. You could pass in text "(1,2,3)", build your query and
use EXECUTE to execute it. 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 was not able to find a reference whether this is possible and if yes
> how to specify the argument type and how to call this function to hand over
> the list for the IN clause correctly.
>
> Next question. Is there any example how to return a SETOF MyTable in
> a plpgsql function? I tried
>
> CREATE FUNCTION test2()
> RETURNS setof MyTable
> AS '
> DECLARE
> result SETOF MyTable ;
> BEGIN
> result := (SELECT * FROM MyTable);
> RETURN result ;
> END; ' LANGUAGE 'plpgsql';
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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
@ 2003-11-27 08:42 ` Andreas Tille <tillea@rki.de>
2003-11-27 15:57 ` Re: Scaler forms as function arguments Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 8+ messages in thread
From: Andreas Tille @ 2003-11-27 08:42 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
2003-11-27 08:42 ` Re: Scaler forms as function arguments Andreas Tille <tillea@rki.de>
@ 2003-11-27 15:57 ` Joe Conway <mail@joeconway.com>
2003-11-27 16:40 ` Re: Scaler forms as function arguments Andreas Tille <tillea@rki.de>
0 siblings, 1 reply; 8+ messages in thread
From: Joe Conway @ 2003-11-27 15:57 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: pgsql-sql
Andreas Tille wrote:
> test=# select PLpgSQLDepartmentSalaries() ;
This should be:
regression=# select * from PLpgSQLDepartmentSalaries();
departmentid | totalsalary
--------------+-------------
1 | 80000
2 | 60000
(2 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
2003-11-27 08:42 ` Re: Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-27 15:57 ` Re: Scaler forms as function arguments Joe Conway <mail@joeconway.com>
@ 2003-11-27 16:40 ` Andreas Tille <tillea@rki.de>
2003-11-27 17:31 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Tille @ 2003-11-27 16:40 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
On Thu, 27 Nov 2003, Joe Conway wrote:
> Andreas Tille wrote:
> > test=# select PLpgSQLDepartmentSalaries() ;
>
> This should be:
> regression=# select * from PLpgSQLDepartmentSalaries();
> departmentid | totalsalary
> --------------+-------------
> 1 | 80000
> 2 | 60000
> (2 rows)
Well, it is easy to understand what it 'should be' reading the code - but
it throws the error message I posted. Is this possibly a feature of a higher
PostgreSQL version than 7.3.2?
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
2003-11-27 08:42 ` Re: Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-27 15:57 ` Re: Scaler forms as function arguments Joe Conway <mail@joeconway.com>
2003-11-27 16:40 ` Re: Scaler forms as function arguments Andreas Tille <tillea@rki.de>
@ 2003-11-27 17:31 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2003-11-27 17:31 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: pgsql-sql
On Thursday 27 November 2003 16:40, Andreas Tille wrote:
> On Thu, 27 Nov 2003, Joe Conway wrote:
> > Andreas Tille wrote:
> > > test=# select PLpgSQLDepartmentSalaries() ;
> >
> > This should be:
> > regression=# select * from PLpgSQLDepartmentSalaries();
> > departmentid | totalsalary
> > --------------+-------------
> > 1 | 80000
> > 2 | 60000
> > (2 rows)
>
> Well, it is easy to understand what it 'should be' reading the code - but
> it throws the error message I posted. Is this possibly a feature of a
> higher PostgreSQL version than 7.3.2?
No - look carefully at Joe's response. He's calling it like:
SELECT * FROM my_function();
You treat the function like a table.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
@ 2003-11-28 05:47 ` Joe Conway <mail@joeconway.com>
2003-11-28 16:48 ` Re: Scaler forms as function arguments Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 8+ messages in thread
From: Joe Conway @ 2003-11-28 05:47 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Andreas Tille <tillea@rki.de>; pgsql-sql
Richard Huxton wrote:
> On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
>>I want to write a function of the following type
>>
>> CREATE FUNCTION test ( <scalar form type> )
>> RETURNS setof MyTable
>> AS
>> 'SELECT * FROM MyTable WHERE id IN $1'
>> LANGUAGE 'SQL' ;
>
> Not as you've done it. You could pass in text "(1,2,3)", build your query and
> use EXECUTE to execute it. Alternatively, you might be able to do it with an
> array parameter (sorry, I don't use arrays, so I can't be sure).
In 7.4 you could use an array. It would look like this:
CREATE TABLE mytable (id int, idval text);
INSERT INTO mytable VALUES (1,'a');
INSERT INTO mytable VALUES (2,'b');
INSERT INTO mytable VALUES (3,'c');
CREATE FUNCTION test (int[]) RETURNS setof MyTable AS '
SELECT * FROM mytable WHERE id = ANY ($1)
' LANGUAGE 'SQL' ;
regression=# SELECT * FROM test(ARRAY[1,3]);
id | idval
----+-------
1 | a
3 | c
(2 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Scaler forms as function arguments
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Re: Scaler forms as function arguments Richard Huxton <dev@archonet.com>
2003-11-28 05:47 ` Re: Scaler forms as function arguments Joe Conway <mail@joeconway.com>
@ 2003-11-28 16:48 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Greg Stark @ 2003-11-28 16:48 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Richard Huxton <dev@archonet.com>; Andreas Tille <tillea@rki.de>; pgsql-sql
Joe Conway <mail@joeconway.com> writes:
> In 7.4 you could use an array. It would look like this:
Though note that 7.4 doesn't know how to optimize this form:
db=> explain select * from foo where foo_id in (1,2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using foo_pkey, foo_pkey on foo (cost=0.00..6.05 rows=2 width=756)
Index Cond: ((foo_id = 1) OR (foo_id = 2))
(2 rows)
db=> explain select * from foo where foo_id = ANY (array[1,2]);
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1132.82 rows=5955 width=756)
Filter: (foo_id = ANY ('{1,2}'::integer[]))
(2 rows)
--
greg
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-11-28 16:48 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-26 15:40 Scaler forms as function arguments Andreas Tille <tillea@rki.de>
2003-11-26 17:25 ` Richard Huxton <dev@archonet.com>
2003-11-27 08:42 ` Andreas Tille <tillea@rki.de>
2003-11-27 15:57 ` Joe Conway <mail@joeconway.com>
2003-11-27 16:40 ` Andreas Tille <tillea@rki.de>
2003-11-27 17:31 ` Richard Huxton <dev@archonet.com>
2003-11-28 05:47 ` Joe Conway <mail@joeconway.com>
2003-11-28 16:48 ` Greg Stark <gsstark@mit.edu>
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