Ciekawy cast

Link

Ciekawy CAST
Type Conversion: CREATE CAST
Values and objects can be converted between types using casts. Casts can be explicit only, implicit on assignment, and implicit generally. In some cases casts can be dangerous.

The overall syntax of CREATE CAST is discussed in the PostgreSQL manual. In general almost all user created casts are likely to use a SQL function to create one to another. In general explicit casts are best because this forces clarity in code and predictability at run-time.

For example see our previous country table:

or_examples=# select * from country;
id | name | short_name
----+---------------+------------
1 | France | FR
2 | Indonesia | ID
3 | Canada | CA
4 | United States | US
(4 rows)

Now suppose we create a constructor function:

CREATE FUNCTION country(int) RETURNS country
LANGUAGE SQL AS $$
SELECT * FROM country WHERE id = $1 $$;

We can show how this works:

or_examples=# select country(1);
country
---------------
(1,France,FR)
(1 row)

or_examples=# select * from country(1);
id | name | short_name
----+--------+------------
1 | France | FR
(1 row)

Now we can:

CREATE CAST (int as country)
WITH FUNCTION country(int);

And now we can cast an int to country:

or_examples=# select (1::country).name;
name
--------
France
(1 row)

The main use here is that it allows you to pass an int anywhere into a function expecting a country and we can construct this at run-time. Care of course must be used the same way as with dereferencing foreign keys if performance is to be maintained. In theory you could also cast country to int, but in practice casting complex types to base types tends to cause problems than it solves.