Re: automatic value conversion - Mailing list pgsql-general

Am 24.03.2007 um 19:08 schrieb Tom Lane:

> Sebastian Boehm <pg@seb.exse.net> writes:
>> the functions function_in and function_out should be called
>> automatically on select or insert / update.
>> How can I change the table definition to use function_in and
>> function_out transparently for me (as if the table had a text colum)
>
> You would have to create a new data type and make those functions
> be the I/O functions of the type.

in my example you can see that the two functions have two arguments.

How do I do this with new data types ? (I researched in data types
already)


the second argument is fixed, but differs from column to column and
from table to table.

can you make a short example based to the example I have given.


imageing a table with two rows

create table table1
(
    row1 integer,
    row2 integer
)

the in and out functions for the two rows would be the same, but the
second argument to that function differs between them. with select
insert and update statements I want to use text as datatype for row1
and row2 the in and out functions need to convert them.


>
> It sorta looks to me like you are reinventing the concept of an ENUM
> column.  This has been done before --- see enumkit
> http://archives.postgresql.org/pgsql-hackers/2005-10/msg01243.php
> as well as the currently pending patch to integrate the feature
> into core Postgres.

Right, its is quite similar, but I want to to something which is not
possible with enum. Its like enum but it expands dynamically, I
attached the in and out functions I want to use.
The main thing is that it needs to insert new entries into the enum
when someone inserts something that did not exits before.

Its like a textcolumn that is internaly represented as an integer,
and a map , mapping the integer to the textvalue and expanding that
map if someone stores a textvalue that did not exist before in that
map. It should be possible to apply this to every textcolumn. it
makes sense when you have textcolumns with millions of rows but only
a couple of differend values and you cannot change the application
code. sometimes new values appear, so a normal enum would not work.
As the textvalue itself is quite big, this will reduce the size
(storage) of the table dramatically. Currently the table has a couple
of textcolumns, with only a few differend values, I would like to
change that, so that only intergers are stored in the table in a way
that is completely transparent to that existing application.

thank you
sebastian

>
>             regards, tom lane
>

----------------------------


CREATE FUNCTION valuemap_in(colname_in text, value_in text) RETURNS
integer
     AS $$
     DECLARE retval integer;
     BEGIN
     SELECT valueid FROM valuemap WHERE colname = colname_in AND
value = value_in INTO retval;
     IF (retval IS NULL) THEN
         INSERT INTO valuemap (colname,value,valueid) VALUES
(colname_in,value_in,(SELECT coalesce(max(valueid),0)+1 FROM valuemap
WHERE colname = colname_in));
         SELECT valueid FROM valuemap WHERE colname = colname_in AND
value = value_in INTO retval;
     END IF;
     RETURN retval;
     END;
$$
     LANGUAGE plpgsql;

CREATE FUNCTION valuemap_out(colname_in text, valueid_in integer)
RETURNS text
     AS $$
     DECLARE retval text;
     BEGIN
     SELECT value FROM valuemap WHERE colname = colname_in AND
valueid = valueid_in
         INTO retval;
     RETURN retval;
     END;
$$
     LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE valuemap (
     id integer DEFAULT nextval('object_id_seq'::text) NOT NULL,
     colname text NOT NULL,
     value text NOT NULL,
     valueid integer NOT NULL
);
ALTER TABLE ONLY valuemap ADD CONSTRAINT valuemap_pkey PRIMARY KEY (id);
CREATE UNIQUE INDEX valuemap_colname_value_pkey ON valuemap USING
btree (colname, value);
CREATE UNIQUE INDEX valuemap_colname_value_valueid_ukey ON valuemap
USING btree (colname, value, valueid);
CREATE INDEX valuemap_colname_valueid_key ON valuemap USING btree
(colname, valueid);