Postgres Cast Composite Types

Question

I'm facing a strange behaviour when using a function to cast to composite type in Postgres 9.6.

I have declared a composite type "vector" as x,y,z - each of double precision as well as a cast as follows:

create type vector as(
 x double precision,
 y double precision,
 z double precision
);

create cast (text as vector)
 with function vector_from_text(text) as implicit;

Function vector_from_text looks like this:

create or replace function vector_from_text(text, out result vector) strict immutable language plpgsql as $$
declare
    d double precision[];
begin
    result := null;
    if ($1 is null) then
        return;
    end if;

    begin
        with c1 as (
               select $1::jsonb src
        )
        select row((src->>'x')::double precision, (src->>'y')::double precision, (src->>'z')::double precision)::vector
        **into result** -- see below
        from c1;
    exception
        when others then
            d := translate($1, '()', '{}')::double precision[];
            result := row(d[1], d[2], d[3])::vector;
    end;
end$$
;

The function returns null on null, or a vector type for both input formats either a json-string like '{"x":0, "y":0, "z":0}' or a constructor expression like '(0,0,0)'.

The Problem:

For json-like inputs the functions returns the error

invalid input syntax for type double precision: "(0,0,0)"

as soon the select statement contains the line into result. If I remove this line or change the output variable from result to something of type text the functions works as expected.

Why is it not possible to assign an already to type vector casted value into a vector? Don't get it!

klin · Accepted Answer · 2017-11-02 15:58:28Z

You do not need to (and in fact should not) create a cast from text. When you create a composite type you can cast a text to the type without any additional steps:

create type my_record as(
    x int,
    y int,
    z int
);

select '(1,2,3)'::my_record;

 my_record 
-----------
 (1,2,3)
(1 row)

If you want to use jsonb, create a cast from jsonb to the type:

create or replace function my_record_from_jsonb(obj jsonb, out result my_record) 
strict immutable language plpgsql as $$
begin
    select (obj->>'x')::int, (obj->>'y')::int, (obj->>'z')::int
    into result;
end
$$;

create cast (jsonb as my_record)
    with function my_record_from_jsonb(jsonb);

select '{"x":1, "y":2, "z":3}'::jsonb::my_record;

 my_record 
-----------
 (1,2,3)
(1 row)

Do not try to interpret text literals in two different ways. If you want to use jsonb syntax, use jsonb type. Creating a custom implicit cast from text is particularly unreasonable. Read in the documentation::

It is wise to be conservative about marking casts as implicit. An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands, or to be unable to resolve commands at all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same general type category. For example, the cast from int2 to int4 can reasonably be implicit, but the cast from float8 to int4 should probably be assignment-only. Cross-type-category casts, such as text to int4, are best made explicit-only.

Collectives™ on Stack Overflow

Postgres Cast Composite Types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related