[GENERAL] Custom types and array equality

2006-10-20 Thread Morgan Kita
Hi,

This should be a relatively simple question... I just made a custom type
in C, which I will use to build an array in one of my tables. I defined
an equality function in C, and declared an operator = on my custom type
that calls my equality operator.

Along the lines of:
CREATE OPERATOR = (
   leftarg = mytype, rightarg = mytype, procedure = mytype_equal,
   commutator = = 
);

However, I cant seem to add a useful unique constraint on the table that
includes a column of type mytype[]. When I insert a new record it
complains that it can't find a comparison operator for the type. I did a
bit of digging and found in array.c that error message is produced when
calling array_cmp and the type has no defined comparison operator...
However, should array_eq be called for unique constraints? What am I
missing here to get this system working? The operator itself works when
I call it explicitly on two arrays of mytype.

Thanks for any help ahead of time!


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


[GENERAL] Foreign keys, arrays, and uniqueness

2006-10-02 Thread Morgan Kita
Let me start by saying I understand that postgresql does not support the
following: composite data types with individual components acting as
foreign keys, arrays of composite data types, and arrays with elements
acting as foreign keys. I will layout my example using them for clarity
even though they don't exist

Ok let me layout a rough example of my problem:

CREATE TABLE target_node1 (
id int4,
value text
);

CREATE TABLE target_node2 (
id int4,
value text
);

CREATE TYPE composite1 (
idx int4,
reference int4
);

CREATE TYPE composite2 (
idx int4,
reference1 int4
reference2 int4
);

CREATE TABLE example_table (
id int4,
value test,
type1 composite1[];
type2 composite2[];
);

ALTER TABLE example_table ADD FOREIGN KEY ((composite1).reference)
REFERENCES target_node1 (id);
ALTER TABLE example_table ADD FOREIGN KEY ((composite2).reference1)
REFERENCES target_node1 (id);
ALTER TABLE example_table ADD FOREIGN KEY ((composite2).reference2)
REFERENCES target_node2 (id);

In addition I want a UNIQUE check constraint on INSERT and UPDATE that
first checks that the text in the value column is unique. If that fails
then I need to check that the type1 and type2 arrays are unique and act
accordingly.

Yes I understand all of that is completely wrong, but hopefully it
clarifies my intentions.

Essentially, I want a list(s) of a custom type(hopefully composite type)
where individual data members of the type are foreign keys to other
tables. Also I need to use those custom types for uniqueness in
combination with other information in the table.

The solutions as I see it:
1. Parallel arrays for the data members of the types. In other words an
array for composite1.idx, an array for composite1.reference and so on.
Then I could take the array of interest and create triggers that would
allow the array to act as a set of foreign keys. However, this is slow
when updating or deleting from a table like target_node1. Also it just
seems accident prone given that the values are all separate from each
other, and hardly human understandable.

2. Normalize this and move the composite type into its own table that
references example_table. It is a many-to-one relationship after all,
and I am sure this is the route that will probably be suggested by you
guys. However... if I do that how the hell do I do maintain my
uniqueness check on insert into example table given that half of the
data I am checking on is in another table, and hasn't been inserted yet
for the new row? I am totally stuck in regards to this...

3. Something with truly custom data types(aka C functions loaded into
the back end). To be honest I don't even know how possible that is or
how much work that would take. I have defined my own custom types
before, but not any indexing functions for them. What would I have to
define beyond the type itself? How would the foreign keys work in this
situation, if at all?

Help! This is seriously important to my project... but naturally the
less complicated the solution the better.

Thanks in advance,
Morgan


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match