Skip site navigation (1) Skip section navigation (2)

Re: Converting stored procedure from mySQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Verena Ruff <lists(at)triosolutions(dot)at>
Cc: pgsql-novice(at)postgresql(dot)org, Andreas Hjortsberg <andreas(dot)hjortsberg(at)x-change(dot)se>
Subject: Re: Converting stored procedure from mySQL
Date: 2006-06-16 21:49:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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)
        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

In response to

pgsql-novice by date

Next:From: raulpdlrDate: 2006-06-16 21:55:00
Subject: FATAL: IDENT authentication failed for user "soAndso", quick fix
Previous:From: Frances CollierDate: 2006-06-16 20:03:51
Subject: Re: windows update screwed up PostgreSQL -- Problem solved

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group