6

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).

3
  • How about you put the definition at the bottom of your code block at the top instead? Commented Jun 29, 2015 at 18:39
  • @Politank: Wouldn't work, it would complain that color_in, doesn't exist. Commented Jun 29, 2015 at 23:24
  • 2
    FYI, one of the Postgres devs wrote a detailed tutorial on user-defined types, and used 24-bit RGB colours as the example. All that's left for you to do is compile it. Commented Jun 30, 2015 at 8:15

1 Answer 1

3

From http://www.postgresql.org/docs/9.4/interactive/datatype-pseudo.html

Functions coded in procedural languages can use pseudo-types only as allowed by their implementation languages. At present the procedural languages all forbid use of a pseudo-type as argument type, and allow only void and record as a result type (plus trigger when the function is used as a trigger). Some also support polymorphic functions using the types anyelement, anyarray, anynonarray, anyenum, and anyrange.

And from the docs for create type http://www.postgresql.org/docs/9.4/interactive/sql-createtype.html

The input function can be declared as taking one argument of type cstring, or as taking three arguments of types cstring, oid, integer.

The output function must return type cstring.

Given the above:

  1. Base data type input and output functions use cstring.
  2. Procedural languages can not use cstring.
  3. Base data type functions can't be written in procedural language.
  4. Defining a base data type can't be done purely in procedural languages.

You can use pre-existing input and output functions, but I don't think that any of them will directly get you a hex string. The closest is probably the \x hex escaped bytea, but you'd have a \x at the beginning of your text representation. If you're willing to cast to and from text, I think you could create a type using bytea_in and bytea_out and writing custom casts to and from text. You'd have to explicitly cast to avoid the \x though.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.