From: Richard Huxton <dev@archonet.com>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>,
	<pgsql-sql@postgresql.org>
Subject: Re: Create View
Date: Wed, 15 Oct 2003 09:17:50 +0100
User-Agent: KMail/1.5
References: <009401c392b7$e5fefe30$1f00a8c0@middinkcomp>
In-Reply-To: <009401c392b7$e5fefe30$1f00a8c0@middinkcomp>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200310150917.50227.dev@archonet.com>

On Wednesday 15 October 2003 02:01, Muhyiddin A.M Hayat wrote:
>
> Table Billing:
>
>         id       trx_date       trx_time     depart       payment_method   
> billing_amount    amount_paid     balance       creator 1      10/09/2003 
> 21:55:02   Resto        Visa                           13.800,00           
>      10.000,00       3.800,00       middink
>
> Table Payment
>
>         id r   trx_date        trx_time        descriptions  
> payment_method   amount      creator 1     10/08/2003  18:17:40     
> Payment          Cash                         2.000,00     middink
>
>
> I would like to create "View " from above table with result look like:
>
>
>          trx_date       trx_time     descriptions                          
>                                   payment_method   debet              
> credit             balance         creator 10/09/2003  21:55:02   Resto 
> Billing  13.800,00 Paid: 10.000,00         Visa                         
> 3.800,00                               3.800,00         middink 10/08/2003 
> 18:17:40    Payment                                                        
>          Cash                                                2.000,00      
> 1.800,00          middink

I'm not sure the above makes sense. I don't see any way to connect that 
particular payment to that particular bill. You also seem to have two 
different types of information in the billing table (billing *and* payment).

Also, the payment seems to be occurring before the bill - which can happen, 
but there doesn't seem to be any account it is paid into.

Generally, there are two ways to do this:
1. Set-returning functions (see the manuals and 
http://techdocs.postgresql.org)
2. A transaction/account page that you keep up to date in exactly the sort of 
format you want in your view. Triggers can make sure this happens 
automatically.

Unfortunately, if you're stuck with the tables you've shown us then I don't 
think you can solve your problem.
-- 
  Richard Huxton
  Archonet Ltd