Cannot assign/cast PostgreSQL record with JSONB to an HSTORE column

91 Views Asked by At

I'm trying to create a trash table, where I can store deleted entities, and have the ability to restore them manually as needed. To make this more complicated, my tables have JSONB, HSTORE, GEOMETRY, and BYTEA columns.

Inserting is pretty simple:

insert into trash (original_table, original_id, content) select 'something', id, to_jsonb(something) from something where ...;

But HSTORE seems to cause problems when I'm trying to read data back from trash:

select t.* from trash, jsonb_to_record(content) as t(id bytea, created_at timestamp, attributes hstore, ...);

(content is a column of type JSONB.)

GEOMETRY, BYTEA, and JSONB columns get assigned/cast just fine. I know that JSONB cannot be automatically cast to HSTORE, so I created this CAST:

CREATE FUNCTION jsonb_to_hstore(j JSONB) RETURNS HSTORE IMMUTABLE STRICT LANGUAGE sql AS $$
    SELECT hstore(array_agg(key), array_agg(value)) FROM jsonb_each_text(j)
$$;

CREATE CAST (JSONB AS HSTORE) WITH FUNCTION jsonb_to_hstore AS IMPLICIT;

But it doesn't seem to be used, and I still get this error when including the HSTORE column in t(...):

ERROR:  Syntax error near '"' at position 11

Why is my cast not used by AS? Where can I find out more about the usage of the record type? jsonb_to_record documentation mentions that:

As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.

But I can't find anything more about this.

0

There are 0 best solutions below