I'm trying to create a type to store color hexes preferably in byte form. I followed all the instructions in postgres docs here: http://www.postgresql.org/docs/9.3/static/sql-createtype.html
and found the part where it says CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ; a bit... unsettling. What goes in the ellipses? Turns out that was justified as I cannot find any example of creating a simple data type without custom PG functions written in C.
My best attempt was this:
CREATE TYPE color;
CREATE FUNCTION color_in(cstring) RETURNS color AS $$
BEGIN
RETURN decode($1::text, 'hex')::color;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE RETURNS NULL ON NULL INPUT;
CREATE FUNCTION color_out(color) RETURNS cstring AS $$
BEGIN
RETURN encode($1::bytea, 'hex')::text;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE RETURNS NULL ON NULL INPUT;
CREATE TYPE color (
INTERNALLENGTH = 3,
LIKE = bytea,
INPUT = color_in,
OUTPUT = color_out
);
This yields the error:
NOTICE: return type color is only a shell
ERROR: PL/pgSQL functions cannot return type color
Similar error if I use Language SQL or default to SPL. The example in-out functions are listed here: http://www.postgresql.org/docs/9.3/static/xtypes.html. The only example functions are written in C. Am I correct in assuming this is the only way to write a UDT in postgres? Is there another way? My goal is to have the colors stored as bytes but have their native text form be hexadecimal (for the purposes of dumping, restoring, and casting from raw).
color_in, doesn't exist.