Thread: Scaler forms as function arguments
Hi,
I want to write a function of the following type
CREATE FUNCTION test ( <scalar form type> ) RETURNS setof MyTable AS 'SELECT * FROM MyTable
WHEREid 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.
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
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); insertinto 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 totalsalaryfrom 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.
Andreas Tille wrote: > test=# select PLpgSQLDepartmentSalaries() ; This should be: regression=# select * from PLpgSQLDepartmentSalaries(); departmentid | totalsalary --------------+------------- 1 | 80000 2 | 60000 (2 rows) HTH, Joe
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.
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
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
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
Scanusing 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