From: | Andreas Hjortsberg <andreas(dot)hjortsberg(at)x-change(dot)se> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Converting stored procedure from mySQL |
Date: | 2006-06-16 13:06:59 |
Message-ID: | 20060616130659.a4b430b8@mail.x-change.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 I also 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-06-16 13:53:11 | Re: Converting stored procedure from mySQL |
Previous Message | Christoph Della Valle | 2006-06-16 07:06:00 | Re: Find records that do not contain an item |