PostgreSQL Notes

PostgreSQL is an open source relational database. While PostgreSQL doesn’t support stored procedures in the same sense that Sybase, MS SQL Server or Oracle does, it does support functions that can return sets of records.

While there is documentation saying how to do this, some of it is a little hard to find. Also I couldn’t find any examples anywhere on the web. So hopefully this page will help others that want to do the same thing.

I’m not sure about other people, but I find that an example of how to do something is often much easier to understand and follow than an explination of how something could be done. So the example that I am showing here is from my own code.

The Example

The context for the example is a wholesale plant nursery. A place that grows plants to sell to retail shops, mail order retail clients or other plant nurseries. An inventory item can be defined as a plant of a particular size, the size often defined by the size of the bag or pot it is in. Now it is possible to have these inventory items stored in different locations around the nursery. When an order is assigned to be dispatched, a list is printed out telling the worker where to get the plants from.

The tricky situation comes when there are multiple locations, none which have sufficent quantity to supply the order. You need to be able to get x from one location and y from another. I wanted to automate this process and a function seemed like the best way to do it.

This example was created and tested using PostgreSQL 8.0 beta 2

The Function definition

CREATE OR REPLACE FUNCTION inventory_picking_locations(itemId_ INTEGER, qty_ INTEGER)
    RETURNS SETOF RECORD AS '
DECLARE
    row_ RECORD;
    cursor_ CURSOR FOR SELECT inv.location_id, inv.quantity FROM inventory_location inv 
          JOIN location loc ON (loc.id = inv.location_id) WHERE inv.inv_id=itemId_ AND
          loc.picking_priority > 0 ORDER BY loc.picking_priority DESC, loc.name;
    qty_left_ INTEGER;
BEGIN
    -- Initialise our quantity to get
    qty_left_ := qty_;

    OPEN cursor_;

    LOOP
        FETCH cursor_ INTO row_;
        EXIT WHEN NOT FOUND;

        -- either this location has enough and we exit
        -- or we take it all and go on to the next location
        IF row_.quantity >= qty_left_ THEN
            row_.quantity := qty_left_;
            RETURN NEXT row_;
            qty_left_ := 0;
            EXIT;
        ELSIF row_.quantity > 0 THEN
            qty_left_ := qty_left_ - row_.quantity;
            RETURN NEXT row_;
        END IF;
    END LOOP;

    CLOSE cursor_;
    RETURN;
END;
' LANGUAGE 'plpgsql';

The bits that make it work

RETURNS SETOF RECORD

This tells PostgreSQL that the function returns potentially many records. The PostgreSQL website says:

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command.

RETURN NEXT

Again we can find the reference here.

When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified in RETURN NEXT commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing.

Calling the function

When selecting from a function that returns SETOF RECORDs, you need to specify what the type is of the RECORD. This is done with the column definition list shown below.

SELECT * FROM inventory_picking_locations(itemId_, qty_)
    AS (location_id INTEGER, quantity INTEGER);

But wait, there’s more…

There has just been a post on pgsql-general which mentioned creating a type and returning a set of types from a function. I’ll look at this and then update the page.

//////////////////////////////

On Thu, 2004-09-23 at 18:28, Tim Penhey wrote:
> Robert Fitzpatrick wrote:
>
> >Can someone point me to some more information or perhaps show an example
> >of returning a recordset from a plpgsql function. I'd like to send an
> >argument or arguments to the function, do some queries to return a set
> >of records. I've done several functions that return one value of one
> >type, but nothing that returns a set.
> >
> >
> Ah ha.  Someone else trying to find out what I had hunted for.  I could
> not find an example on the web, so I started writing one.
> Not fully complete yet, but here is what is there and I hope it helps.
> http://www.scorefirst.com/postgresql.html

//////////////////////////////
http://www.dbtalk.net/comp-databases-postgresql-novice/plpgsql-column-definition-list-98425.html

The result of the function test() should be a list of integer coming from a
column "ech_id" from a recursive function
data.sp_ech_recurs_desc_a(integer).

I have the following message:
ERROR: a column definition list is required for functions returning
"record"

How to define this list in this function ?

Thanks
--------
CREATE OR REPLACE FUNCTION test()
RETURNS SETOF record AS
'
declare
parent integer;
rec RECORD;
begin
... a "SELECT INTO parent ..." giving a value to the variable parent
....
FOR rec IN SELECT ech_id from data.sp_ech_recurs_desc_a(parent) LOOP
RETURN NEXT rec;
END LOOP;
return;
end
'
LANGUAGE 'plpgsql' VOLATILE;