From: Richard Huxton <dev@archonet.com>
To: Muhyiddin A.M Hayat <middink@indo.net.id>
To: pgsql-sql@postgresql.org
Subject: Re: Create View
Date: Wed, 15 Oct 2003 09:17:50 +0100
Message-ID: <200310150917.50227.dev@archonet.com> (raw)
In-Reply-To: <009401c392b7$e5fefe30$1f00a8c0@middinkcomp>
References: <009401c392b7$e5fefe30$1f00a8c0@middinkcomp>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, middink@indo.net.id
Subject: Re: Create View
In-Reply-To: <200310150917.50227.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox