Hello,
I'm trying to convert one stored procedure from mysql but I have some problems to get it runing.
It's quite basic but I haven't found the answer in the docs or on the mailing lists.
So here is my function
CREATE OR REPLACE FUNCTION sp_order_amount_per_office(accountingid IN INTEGER, code OUT VARCHAR,officetotal OUT
NUMERIC)as $$
BEGIN
Select office.code as code, sum(transaction.countervaluecustomer) as officetotal
FROM office
LEFT OUTER JOIN receipt
ON (receipt.officeid=office.officeid)
LEFT OUTER JOIN transaction
ON (transaction.receiptid = Receipt.receiptid) and (receipt.accountingid=accountingid)
GROUP BY code order by code;
END ;
$$ LANGUAGE plpgsql;
I can run the select query in pqAdmin and it give me the correct result, but then I try to run it inside my function as
Ialso can run independently I get the following error message
ERROR: syntax error at or near "$1" at character 25
QUERY: SELECT office.code as $1 , sum(transaction.countervaluecustomer) as $2 FROM office LEFT OUTER JOIN receipt
ON(receipt.officeid=office.officeid) LEFT OUTER JOIN transaction ON (transaction.receiptid = Receipt.receiptid) and
(receipt.accountingid=$3 ) GROUP BY $1 order by $1
CONTEXT: SQL statement in PL/PgSQL function "sp_order_amount_per_office" near line 16
It's my first day with postgre so it is probably something very simple
Regards
Andreas