Re: Lookups

On Thu, Oct 19, 2006 at 09:38:35AM +1300, Layton Duncan wrote:
> I'm new to Postgres coming from a Filemaker Pro background. I'm
> looking at converting an existing database from FM to postgres.
>
> One thing i'm not sure on, is how best to implement an equivalent of
> a FileMaker auto-enter "lookup" in Postgres.
>
> Basically, i'm wanting to set the contents of a field in one table
> based on the contents of a related field in another table.
>
> Say for example in a table "Jobs", i enter a customer id, I then want
> the "customer name" field in the same table ("Jobs") to have the
> appropriate name pulled from the "Customers" table, based on the
> customer id. I don't want to leave this up to client processing, I
> would like it done server side.

While you could do this with triggers, it's bad design. What happens if
the customer name changes in the parent table?

You'd be much better off creating a view that joins the two tables and
keeping your schema properly normalized.

> Is this done with triggers? Is there any information on writing
> trigger functions in postgres "internal" language? I've found a lot
> on writing trigger functions in c, but although i'm completely
> competent in c, it would be nice to avoid it.

Well, there is no 'internal' language as such (other than pure SQL,
which you can't use for this), as all procedural languages are just
plugins. But plpgsql is as close to a standard as you'll get, and
there's info about using it to write trigger functions in the plpgsql
section of the docs.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)