Converting stored procedure from mySQL

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

Responses

Browse pgsql-novice by date

  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