From: Wolfe Whalen <wolfe@quios.net>
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: unique keys / foreign keys on two tables
Date: Thu, 29 Nov 2012 03:13:17 -0800
Message-ID: <1354187597.1531.140661159631189.17EE50C9@webmail.messagingengine.com> (raw)
In-Reply-To: <201211291014.27989.gary.stainburn@ringways.co.uk>
References: <201211291014.27989.gary.stainburn@ringways.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Hi Gary,
The most straightforward way to ensure that the two tables have unique
IDs would be to create one sequence called something like
"destination_seq" and have the id column in both tables default to
NEXTVAL('destination_seq').
As far as storing the destinations go, I'm guessing that you're looking
for a good way to tell what type of id it is without checking both
tables to see which one it exists in. If you need to be able to extract
the destination type from the ID, you could go with something more
robust like:
CREATE OR REPLACE FUNCTION dest_nextval (dest_type int) RETURNS int4 AS
$$
BEGIN
RETURN (nextval('destination_seq') << 1) | dest_type;
END;
$$LANGUAGE plpgsql;
That would use the same destination_seq value, but it it would shift the
number 1 bit to the left. So you could use DEFAULT dest_nextval(0) for
Extensions and DEFAULT dest_nextval(1) for Groups. Your IDs would still
be 100% unique, but you could test for the type by checking IF (id & 1)
= 1 or 0.
The | is a "bitwise" operator for "OR", and the & is the bitwise
operator for AND. They're covered a bit in section 9.3 of the
documentation, but if you went this route you'd probably want to look up
a more thorough explanation of bitwise operations in general.
The same principle works with larger numbers if you needed more types.
If you shifted two bits, you could | by 0, 1, 2, or 3. Shifting 3 bits
would give you 8 possibilities and so on. It's a pretty neat way to
"encode" other information into one field.
I hope that helps!
Best,
Wolfe
--
Wolfe Whalen
wolfe@quios.net
On Thu, Nov 29, 2012, at 02:14 AM, Gary Stainburn wrote:
> I'm designing the schema to store a config from our switchboards.
>
> As with all PBX's the key is the dialed number which is either an
> extension
> number or a group (hunt/ring/pickup) number.
>
> I have two tables, one for extensions and one for groups, basically
>
> ext_id int4 primary key
> ext_desc text
> ....
> ....
> ....
>
> and
>
>
>
> grp_id int4 primary key
> grp_desc text
> .....
> .....
> .....
>
> I now need to be able to ensure the id field is unique across both
> tables.
> Presumably I can do this with a function and a constraint for each table.
> Does anyone have examples of this?
>
>
> Next I have other tables that refer to *destinations* which will be an ID
> that
> could be either an extension or a group. Examples are 'Direct Dial In'
> numbers which could point to either. How would I do that?
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: wolfe@quios.net, gary.stainburn@ringways.co.uk
Subject: Re: unique keys / foreign keys on two tables
In-Reply-To: <1354187597.1531.140661159631189.17EE50C9@webmail.messagingengine.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