Thread: Scaler forms as function arguments

Scaler forms as function arguments

From
Andreas Tille
Date:
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.


Re: Scaler forms as function arguments

From
Richard Huxton
Date:
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


Re: Scaler forms as function arguments

From
Andreas Tille
Date:
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.


Re: Scaler forms as function arguments

From
Joe Conway
Date:
Andreas Tille wrote:
> test=# select PLpgSQLDepartmentSalaries() ;

This should be:
regression=# select * from PLpgSQLDepartmentSalaries(); departmentid | totalsalary
--------------+-------------            1 |       80000            2 |       60000
(2 rows)

HTH,

Joe




Re: Scaler forms as function arguments

From
Andreas Tille
Date:
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.


Re: Scaler forms as function arguments

From
Richard Huxton
Date:
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


Re: Scaler forms as function arguments

From
Joe Conway
Date:
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




Re: Scaler forms as function arguments

From
Greg Stark
Date:
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