CODEWITHSUNDEEP
postgresqlpostgresql-9.6
user1602492
user1602492

Reputation:

Casting to custom type in SELECT query

I am new to PostgreSQL and absolutely lost here. (Guess even the title is absolutely wrong...)

I have e.g. this:

postgres=# SELECT round(10000::decimal/100, 4);
  round   
----------
 100.0000
(1 row)

Is there an easy way to get the same result using a custom type:

postgres=# SELECT 10000::my_type;
----------
 100.0000
(1 row)

Upvotes: 3

Views: 1998

Answers (2)

Hitobat
Hitobat

Reputation: 3037

I think it depends on the type. For example, the following works for me on Postgres 9.3

# CREATE TYPE foo AS (num INT);
CREATE TYPE

# SELECT 300::foo;
ERROR:  cannot cast type integer to foo
LINE 1: SELECT 300::foo

# SELECT (ROW(300)::foo).num;
 num 
-----
 300
(1 row)

Upvotes: 1

klin
klin

Reputation: 121644

Formally you can create any type using this simplified syntax:

CREATE TYPE my_numeric (
    INPUT = my_numeric_in,
    OUTPUT = my_numeric_out,
    <optional parameters> 
    )

where my_numeric_in and my_numeric_out are support functions. The problem is that

Generally these functions have to be coded in C or another low-level language.

This means that it is a kind of extending Postgres and you have to compile the server or an extension with the code of the functions, so it is definitely not an easy way. In practice this kind of input conversions might be done using triggers, however the described conversion looks strange and may be considered bad practice.

You may be tempted to define your type as domain and create appropriate cast but this will not work:

A cast to or from a domain type currently has no effect. Casting to or from a domain uses the casts associated with its underlying type.

The easiest way to achieve a similar effect is to store the value as numeric and use a conversion function, e.g.:

create or replace function my_numeric(numeric)
returns numeric language sql immutable as $$
    select round($1/100, 4)::numeric
$$;

select my_numeric(10000);

 my_numeric 
------------
   100.0000
(1 row)

Upvotes: 0

Related Questions