Re: Converting stored procedure from mySQL

Verena Ruff <lists(at)triosolutions(dot)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