How to use original Postgres input/output_function in CREATE TYPE?

Question

I have a table with some column of type smallint and want to provide a CAST from varchar to smallint to implement some conversions for that column only. So to be able to create a specific CAST for my needs, I need a type for that special column. Already tried using a domain, but Postgres warns about those being ignored in a CAST... So it looks like I'm stuck with CREATE TYPE, but I don't want to implement the required input/output_function on my own, as in the end I only need whatever should already be available for smallint in Postgres.

The problem is I don't know the names of those functions, in which lib those are stored, if I need to provide paths which can vary upon installation on different OS or if those are available at all.

So, is it possible to CREATE TYPE something like smallint which completely only uses Postgres functions and that in a platform/path independent manner?

I didn't find anyone doing something like that. Thanks!

Laurenz Albe · Accepted Answer · 2017-07-19 12:20:29Z

5

You can create a type that is just like smallint like this:

CREATE TYPE myint;

CREATE FUNCTION myintin(cstring) RETURNS myint
   LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int2in';

CREATE FUNCTION myintout(myint) RETURNS cstring
   LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int2out';

CREATE FUNCTION myintrecv(internal) RETURNS myint
   LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int2recv';

CREATE FUNCTION myintsend(myint) RETURNS bytea
   LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int2send';

CREATE TYPE myint (
   INPUT = myintin,
   OUTPUT = myintout,
   RECEIVE = myintrecv,
   SEND = myintsend,
   LIKE = smallint,
   CATEGORY = 'N',
   PREFERRED = FALSE,
   DELIMITER = ',',
   COLLATABLE = FALSE
);

You'd have to define casts to other numeric types if you want to use it in arithmetic expressions.

If you also add casts from varchar (or text), but beware that creating too many casts can lead to ambiguities and surprising behaviour during type resolution. This is the reason why many type casts were removed in PostgreSQL 8.3, see the release notes.

I'd recommend that you look for a simpler solution to your problem, such as explicit type casts.

answered Jul 19, 2017 at 12:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

Deb Over a year ago

I want to create a type with two bigint. return one bigint which is a summation of the two bigints.

Laurenz Albe Over a year ago

@Debjit Creative. Can certainly be done by writing C code (see the example in the documentation).

Deb Over a year ago

Can't we do that using SQL only with no C ?

Laurenz Albe Over a year ago

Definitely not.

Collectives™ on Stack Overflow

How to use original Postgres input/output_function in CREATE TYPE?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related