Re: Converting stored procedure from mySQL

Verena Ruff <lists@triosolutions.at> writes:
> Am Freitag, 16. Juni 2006 17:39 schrieb Andreas Hjortsberg:
>> Basicly I would like to return all the offices and the amount of their
>> orders even if they are null.

> Return to what? To the client application? In this case you should
> consider using a view instead of this function. As you say you're
> migrating from MySQL and MySQL didn't support views in older versions,
> maybe you came up with this workaround.

A view would work nicely.  If you really want this to be a function,
it needs to return a set of rows not just one row, and ISTM you don't
even need plpgsql: a SQL-language function would be easier.

CREATE OR REPLACE FUNCTION sp_order_amount_per_office(accountingid IN INTEGER,
        code OUT VARCHAR,officetotal OUT NUMERIC)
        RETURNS SETOF RECORD
        as $$
Select office.code, sum(transaction.countervaluecustomer)
        FROM office
        LEFT OUTER JOIN receipt
        ON (receipt.officeid=office.officeid)
        LEFT OUTER JOIN transaction
        ON  (transaction.receiptid = Receipt.receiptid) and
            (receipt.accountingid= $1)
        GROUP BY code order by code;
$$ LANGUAGE sql;

A call would look like
SELECT * FROM sp_order_amount_per_office(42);

            regards, tom lane