Converting stored procedure from mySQL
От | Andreas Hjortsberg |
---|---|
Тема | Converting stored procedure from mySQL |
Дата | |
Msg-id | 20060616130659.a4b430b8@mail.x-change.se обсуждение исходный текст |
Ответы |
Re: Converting stored procedure from mySQL
|
Список | 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 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
В списке pgsql-novice по дате отправления: