Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the manual for creating UDTs in postgresql I found the following statement:

PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this information must be capable of being packed into a single non-negative integer value for storage in the system catalogs.

So is there really no way, to store more information that would fit in a non-negative integer? I would like e.g. to be able to store the name of a function that should be called as a default upon insertion on the data (since there might be multiple functions that could work on that data). That is not possible?

share|improve this question
    
"a function that should be called as a default upon insertion on the data" - that's called a trigger. I have no idea what you are talking about. Can you show us an example? –  a_horse_with_no_name Feb 26 '13 at 12:53
    
Sorry for being unclear! Yes, I was thinking of a trigger; but is it possible that the name of the trigger is specified with the data declaration? E.g. assume there are multiple triggers (t1, t2,...), and a udt (my_udt): is it possible that at the time of creation of the table the user specifies ´CREATE TABLE test ( attribute my_udt t1)´ and a function in my_udt interprets that t1 as being the name of a trigger to be added? –  navige Feb 26 '13 at 12:59
    
@navititious No. A trigger is coupled to a table (or column(s) of a table, no matter what data type those columns are. –  dezso Feb 26 '13 at 13:04
add comment

2 Answers

up vote 3 down vote accepted

The system catalog for types, pg_catalog.pg_type, has the following column:

typtypmod integer not null

as you can see by running \d pg_type.

There is no way to store something you can't fit into an integer because that's how the catalog stores typmods.

The non-negative requirement appears to stem from the use of -1 as reserved value.

The specific problem you describe sounds better suited to the addition of a column trigger. It sounds like what you really want is DOMAINs and the ability to add triggers on domains - but unfortunately at this point there is no support for triggers on domains.

share|improve this answer
    
Makes very much sense! Thanks for this very good explanation! –  navige Feb 26 '13 at 14:11
add comment

After quite a lot of research I found a further possibility:

While it is truly not possible to store more information within pg_type, since it is of type integer, there's the option to store information within an attribute of a table.

However, for that purpose you have to change the source code of postgresql: The file reloptions.c allows to add in a key-value format key=value acceptable fields. Using the SQL SET command (e.g. ALTER TABLE tbl ALTER COLUMN col SET (k1 = v1)) the key-values can be set. This information is then stored in the attoptions field in pg_attribute (and not in pg_type.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.