Tuesday, December 7, 2010

CREATE FUNCTION: return types

In the last post we covered the basic anatomy of a SQL function or stored procedure with CREATE FUNCTION:

CREATE FUNCTION function_name (argument_name argument_type, argument_name argument_type, ...) RETURNS return_type AS $$
     function_body_query;
$$ language language_name;

In this post we'll add some additional options and features related to the function's output.

VOID FUNCTIONS
Functions can return nothing. To do that we declare them as "RETURNS void". This is appropriate for non-SELECT commands such as INSERT:

CREATE FUNCTION addsalesdata(name varchar, month varchar, value decimal) returns void as $$
    insert into salesfigures values($1,$2,$3);
$$ language 'sql';


For these functions one can call SELECT function_name(...) or SELECT * FROM function_name(...).

FUNCTIONS THAT RETURN A SINGLE VALUE
Functions can return one value (one int, one decimal, one text etc.). For these functions one can call SELECT function_name(...) or SELECT * FROM function_name(...).

CREATE FUNCTION totalsalesformonth(month varchar) returns decimal as $$
    select sum(sales) from salesfigures where month=$1;
$$ language 'sql';


Another option to define a function's output is using OUT parameters. Here we do not use RETURNS but we list the order, names and types of output alongside the input arguments preceding each field with "OUT".

CREATE FUNCTION salesforpersonmonth4(month varchar, OUT name varchar, OUT value decimal) as $$
    select name,sales from salesfigures where month=$1;
$$ language 'sql';

aggs=# select * from salesforpersonmonth4('jan');
 name | value
------+-------
 joe  |    34
(1 row)


Notice that this is returning just one record when in fact the query would produce multiple rows of results.

FUNCTIONS THAT RETURN A SETOF VALUES
Functions can also return a set of values of the same type. That is, imagine it returning a column of decimals or a column of text. To specify this we use the SETOF modifier. Here is a function that returns the sales figures for each salesperson for January.

CREATE FUNCTION salesformonth(month varchar) returns SETOF decimal as $$
    select sales from salesfigures where month=$1;
$$ language 'sql';


aggs=# select * from salesformonth('jan');
 salesformonth
---------------
            34
            18
(2 rows)

FUNCTIONS THAT RETURN A SETOF ROWS
There are 4 options to return a set of rows.
1) RETURNS table
PostgreSQL functions (version 8.4 and later) can also return a table. That is, we define the fields of the SETOF rows that the function will return. Here is that last function but with the person's name as well as their sales:

CREATE FUNCTION salesforpersonformonth(month varchar) returns table(name text, sales decimal) as $$
    select name, sales from salesfigures where month=$1;
$$ language 'sql';

aggs=# select * from salesforpersonformonth('jan');
 name | sales
------+-------
 joe  |    34
 bob  |    18
(2 rows)

The types that functions return can be standard PostgreSQL types but they can also be columns or tables that exist or they can be a type that the user defines.

2) Existing table
Here we have a table with a name and sales value:

aggs=# create table personsales(name varchar, sales decimal);
CREATE TABLE

A function can then return that type:

CREATE FUNCTION salesforpersonmonth(month varchar) returns SETOF personsales as $$
    select name,sales from salesfigures where month=$1;
$$ language 'sql';

aggs=# select * from salesforpersonmonth('jan');     
 name | sales
------+-------
 joe  |    34
 bob  |    18
(2 rows)

3) Column of existing table
Here is a function that returns a SETOF values of a type defined by the column of an existing table:

CREATE FUNCTION salesforpersonmonth2(month varchar) returns SETOF personsales.sales%TYPE as $$
    select sales from salesfigures where month=$1;
$$ language 'sql';

aggs=# select * from salesforpersonmonth2('jan');
salesforpersonmonth2
----------------------
                   34
                   18
(2 rows)

4) Defined type
We can create a new type by using CREATE TYPE. CREATE TYPE has a lot of options but the basic use is

CREATE TYPE typename AS (fieldname1 type1, fieldname2 type2,...);

For instance,

CREATE TYPE personsales3 AS (name varchar, sales decimal);

Then, one can define

CREATE FUNCTION salesforpersonmonth3(month varchar) returns SETOF personsales3 as $$
    select name,sales from salesfigures where month=$1;
$$ language 'sql';

6 comments:

  1. Thanks so much for this great content!

    I went through all your blog posts.
    I wish the serie could continue to go into intermediate and advanced level stuff.

    You truly helped me a lot to ramp up on PostgreSQL. Thanks again!
    Julien

    ReplyDelete
  2. An excellent set of posts. Very useful. Thank you

    ReplyDelete
  3. Very good for beginners, Will like to see the series going ahead with some advanced stuff.
    It helped me a lot.
    Thanks

    ReplyDelete
  4. excellent.. thanks.. this really help me out understanding posgresql...

    ReplyDelete
  5. Thank you for your contribution. It's easy to understand ,and it revolve my problem. I am moving from MySQL to PostgreSQL and realize PostgreSQL is more powerful over MySQL. I love its OOP concept than data modeling more efficient and flexible.

    ReplyDelete
  6. Can any tell how to use aggs=# ? after creating the proc how to pass the value and get results ?

    aggs=# select * from salesforpersonmonth4('jan');

    ReplyDelete