Datatypes conversion and operators

Postgres datatypes mechanics overview

Type casting

Postgres allows automatic types conversion to some extent. It supports fully automated implicit conversion, more constrained implicit conversion on assignment operation only and explicit conversion specified via CAST function or :: operator.

Being highly extensible system it allows to define custom types and casting rules for them. To demonstrate them in action let's create a record data type which will depict user profiles.

=> create type profile as (id int, name text);
CREATE TYPE

Now we can select generic row record. If its fields match to defined type structure we can explicitly cast it to that type.

=> select row(1, 'John')::profile;
   row
----------
 (1,John)

We can refer to individual fields via standard object attribute dotted syntax.

=> select (row(1, 'John')::profile).id;
 id
----
  1

Just for demonstration purposes we can define cast operations which will return specific object attributes depending on the object usage context. For example, if we want to represent profile object as a number we will return its id attribute. If we will need object string representation we will return its name.

Numeric cast

In order to provide object numeric representation we define cast operation to integer datatype

=> create function profile2int(val profile) returns int as 'select val.id' language sql;
CREATE FUNCTION

=> create cast (profile as integer) with function profile2int;
CREATE CAST

Now we can explicitly cast profile object to integer and it will automatically return object id attribute.

=> select row(10, 'John')::profile::int;
--  row
-- -----
--   10

String cast

Same for string representation. We just need to define another cast function. This time for varchar datatype.

Note: Our type has name attribute defined as having text datatype. Postgres allows implicit conversion between varchar and text datatypes in both sides.

=> create function profile2varchar(val profile) returns varchar as 'select val.name' language sql;
CREATE FUNCTION

=> create cast (profile as varchar) with function profile2varchar;
CREATE CAST

=> select row(10, 'John')::profile::varchar;
--  row
-- ------
--  John

Text representation

We are not limited to just per-attribute scope. We could define any logic we want. For example we want to make custom object string representation. By default postgres has some internal string representation for row objects.

=> select row(10, 'John')::profile::text;
--     row
-- -----------
--  (10,John)

We can override this default approach with the function similar to listed above

=> create function profile2text(val profile) returns text as
$$ select concat('Id: ', val.id, ', Name: ',  val.name) $$ language sql;
CREATE FUNCTION

=> create cast (profile as text) with function profile2text;
CREATE CAST

Now by invoking explicit cast to ::text datatype we will get custom object string representation.

=> select row(10, 'John')::profile::text;
--         row
-- --------------------
--  Id: 10, Name: John

Cast Rules table

All performed customization are stored in the pg_cast system table. Query below shows created casting operations for profile data type.

select t.typname     as source_type
     , t2.typname    as target_type
     , case c.castcontext
          when 'e' then 'explicit cast'
          when 'a' then 'assignment'
          when 'i' then 'implicit'
       end           as when_applied
     , case c.castmethod
          when 'f' then 'function'
          when 'i' then 'type input/output functions'
          when 'b' then 'binary-coercible'
       end           as how_applied
     , p.proname     as function_name
  from pg_cast       c
 inner join
       pg_type       t
    on t.oid         = c.castsource
 inner join
       pg_type       t2
    on t2.oid        = c.casttarget
 inner join
       pg_proc       p
    on p.oid         = c.castfunc
 where t.typname     = 'profile'
;

--  source_type | target_type | when_applied  | how_applied |  function_name
-- -------------+-------------+---------------+-------------+-----------------
--  profile     | int4        | explicit cast | function    | profile2int
--  profile     | varchar     | explicit cast | function    | profile2varchar
--  profile     | text        | explicit cast | function    | profile2text
-- (3 rows)

Operators

The same way as we just defined casting rules we can define operators that can be used with a type. Let's extend our profile type and add to it role attribute which could have one of three values: reader, writer and admin.

=> alter type profile add attribute role varchar;
ALTER TYPE

=> \d profile
               Composite type "public.profile"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 id     | integer           |           |          |
 name   | text              |           |          |
 role   | character varying |           |          |

By applicaiton logic reader has the most limited set of privileges while admin has full set of them. When comparing profile objects we want to put role attribute as a base for comparison. Reader is less than Writer. And Writer is less than Admin.

Greater than ">"

create or replace function role_rank(p_role varchar)
returns int
as $$
   select case p_role
             when 'reader' then 1
             when 'writer' then 2
             when 'admin'  then 3
             else 0
          end;
$$ language sql;

create or replace function profile_compare_gt(l profile, r profile)
returns boolean
as $$
   select role_rank(l.role) > role_rank(r.role);
$$ language sql;

=> create operator > ( function = profile_compare_gt, leftarg = profile, rightarg = profile );
CREATE OPERATOR

Now we can compare profile objects via basic comparison operator >. Comparison is done by string role attribute. Its value is encoded into integer according to application defined rules before actual comparison is performed.

=> select row(1, 'John', 'writer')::profile > row(2, 'Steve', 'reader')::profile;
--  ?column?
-- ----------
--  t

=> select row(1, 'John', 'writer')::profile > row(3, 'Tom', 'admin')::profile;
--  ?column?
-- ----------
--  f

Less than "<"

We can continue and define negation operator <.

create or replace function profile_compare_lt(l profile, r profile)
returns boolean
as $$
   select role_rank(l.role) < role_rank(r.role);
$$ language sql;

=> create operator < ( function = profile_compare_lt, leftarg = profile, rightarg = profile );
CREATE OPERATOR

This gives us possiblity to compare objects the opposite way. According to imposed rules Writer has less power and thus is less than Admin.

=> select row(1, 'John', 'writer')::profile < row(3, 'Tom', 'admin')::profile;
 ?column?
----------
 t

Operators table

User defined operators are stored in pg_operator system table. Query below shows created operators for profile data type.

select o.oprname     as operator_name
     , l.typname     as left_type
     , r.typname     as right_type
     , res.typname   as result_type
  from pg_operator   o
 inner join
       pg_type       l
    on l.oid         = o.oprleft
 inner join
       pg_type       r
    on r.oid         = o.oprright
 inner join
       pg_type       res
    on res.oid       = o.oprresult
 where l.typname     = 'profile'
   and r.typname     = 'profile'
;

--  operator_name | left_type | right_type | result_type
-- ---------------+-----------+------------+-------------
--  >             | profile   | profile    | bool
--  <             | profile   | profile    | bool

Summary

Along with definition of custom types Postgres allows to define a set of casting rules and operators for that type. This makes it possible to use the same basic sql syntax against mentioned types and it will work smoothly as it happens for built-in datatypes and operators. This is an example of great Postgres extensibility available via bare sql commands.

Comments