Re: Chart of Accounts - Mailing list pgsql-general

Sorry for the silence.  Have been away on official duty.  Please see inline:

--- On Mon, 13/10/08, justin <justin@emproshunts.com> wrote:
> <SNIP>
>
> Create Table general_ledger_transactions(
>       transaction_id serial not null
>       coad_id integer,
>       accounting_period integer,
>       debit numeric(20,10) ,
>       credit numeric(20,10),
>       transaction_date datestamp)
> primary key (transaction_id)

A single transaction will often have at least two entities - typically a debit and a credit.  Shouldn't the two (or
howevermay transactions there are) have the same Transaction ID?  This would then lead to essentially having to split
trasactionsinto two tables.  One for the general header information, and another for the line details. 

Ideas on this?


> special note do not use only 2 decimal points in the
> accounting tables.
<SNIP>

> Example would by
>    Select Sum(debits) +
>            Case when  coa.doIhaveChildren then
>                    GetChildAccountDebits(coa.coa_id,
> period_id)
>             else
>                   0.0
>             end;
>    from general_ledger_transactions, coa,
>     where general_ledger_transactions.coad_id = coa.coa_id
>               and coa.coa_id = SomPassedAccountID
>       group by general_ledger_transactions.period_id
>

I tried your function verbatim, but there were so many errors, the function could not even "compile".  I tinkered with
ita little bit and came up with this slightly modified version which gets "compiled": 

CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID integer, PassedPeriodID integer) RETURNS NUMERIC AS
$FunctionCode$
DECLARE retval NUMERIC := 0.0;
BEGIN
    SELECT SUM(gl_transactions.debit) +
        CASE WHEN coa.doIhaveChildren THEN
            GetChildAccountDebits(coa.coa_id, PassedPeriodID )
        ELSE
                   0.0
        END
    INTO retval
    FROM gl_transactions, coa
    WHERE gl_transactions.coa_id = coa.coa_id
            AND coa.parent_id = PassedAccountID
        AND gl_transactions.period_id = PassedPeriodID;

    RETURN retval;
END;
$FunctionCode$
 LANGUAGE 'plpgsql' VOLATILE ;

    (I'll RTFM later to figure out what VOLATILE means :-)

When I try to use the function with a simple select, it fails with the error:

    ERROR:  column "coa.doihavechildren" must appear
    in the GROUP BY clause or be used in an aggregate function

None of the proposed solutions make sense to me.  I understand the error message (aggregation blah, blah).  I just
figurea way to get what I want.  How did you manage to get yours working? 

Thanks
James