Postgrest: Feature request: Add ability to cast an input argument in a POST before INSERT

[a-mckinley]

Hi,

I have a custom postgres type that is nested and relatively complex so I used jsonb_populate_record() server-side to create instances of this type from a json representation.

Currently to INSERT to this table from I would use a custom written server-side function to wrap the call to jsonb_populate_record().

It would be very convenient and versatile if PostgREST allowed you to apply a cast to input arguments e.g.

POST /projects?columns=id,thing::custom_type

which would hopefully apply a server-side cast created with

create function to_custom_type(json(b) object) returns custom_type as 
$$
    select json(b)_populate_record(null::custom_type,object);
$$ language sql immutable parallel safe;

create cast (json(b) as custom_type) with function to_custom_type(json(b)) as implicit;

Is such an enhancement possible? I think it could rather cleanly reduce the amount of specific server-side code needed in this case, but potentially has other uses that I haven't thought of yet.

[wolfgangwalther]

json[b]_populate_record[set] works recursively on composite types: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

But you already know that - that's why you are using it.

We are using it as well to process the json body:
https://github.com/PostgREST/postgrest/blob/9adec12a678afceca78f6bfebba236b031e63487/src/PostgREST/QueryBuilder.hs#L74

If your target table's column is set to your custom type - there is no reason to assume this wouldn't work out of the box. No cast needed.

Does it not work for you?

[steve-chavez]

Related to this gitter thread. In general I think it'd be simple to add. But I'm failing to come up with a use case because, as Wolfgang mentions, casting should already work thanks to json_populate_recordset.

[wolfgangwalther]

In general I think it'd be simple to add.

I don't think so. We don't touch the json before json_populate_recordset. And once this call is over, everything has already been converted to the target table's column types. Now, at this time we could add a cast, but this would either be a cast to the same type the column is already in - or to a type different from the target table's column type. There is literally no point in doing so... :)

We can't hook into the casting that json_populate_recordset does here.

@a-mckinley Did you try just inserting without any custom casts?

[steve-chavez]

@wolfgangwalther I thought of casting it after json_populate_recordset, like:

INSERT INTO "test"."projects" ("id", "name")
SELECT
  "id"::smallint, -- cast here
  "name"
FROM json_populate_recordset (NULL:: "test"."projects" , (SELECT val FROM pgrst_body)) _ RETURNING "test"."projects".*;

There is literally no point in doing so

But yes, I'm also not seeing the use case. I was mostly thinking of https://github.com/PostgREST/postgrest/issues/1597#issuecomment-701563878 (transforming the inputs with a function before the INSERT) when coming up with the implementation.

[wolfgangwalther]

I was mostly thinking of #1597 (comment) (transforming the inputs with a function before the INSERT) when coming up with the implementation.

What about something like https://github.com/PostgREST/postgrest/issues/1661#issuecomment-731744030? Specifically this part:

I can easily see this extended to allow the pre-request function to RETURN json as well. This would even allow some sort of transformation on the input before it is passed to the query.

This is under the assumption, that we move the pre request function in a CTE of the main query together with the SET LOCALs. Once we have that, it is only a small step to doing something similar to this:

INSERT INTO "test"."projects" ("id", "name")
SELECT
  *
FROM json_populate_recordset (NULL:: "test"."projects" , (SELECT pre_request(val) FROM pgrst_body)) _ RETURNING "test"."projects".*;

(note the pre_request(val))

Having a chance to do a transformation before json_populate_recordset kicks in could help in some cases. But after it's done, the ship has sailed.

[wolfgangwalther]

transforming the inputs with a function before the INSERT

A suggestion for that is tracked in https://github.com/PostgREST/postgrest/issues/1661#issuecomment-731744030. Everything else in here seems set.

@a-mckinley Did you try just inserting without any custom casts?

If that doesn't work for you, feel free to re-open.

[steve-chavez]

@wolfgangwalther The pre-request way seems more flexible. But changing the payload would have to be handled on a per-path basis(with conditionals).

The ?columns=col::cast would be much easier in this case. And it's also similar to our ?select=col::cast syntax.

However as I mentioned before, I fail to see the use case where our json_populate doesn't already work.

[a-mckinley]

hi @wolfgangwalther and @steve-chavez. Thanks for talking this one through. You were absolutely correct that jsonb_populate should handle this use case and the initial issue I was seeing was due to a bug in my own code, apologies for that.

[massimiliano-cannata]

Hi all, I've just started looking at postgREST and looks fantastic, but I was looking for handling postGIS extension geometry types. Generally you have a geometry type and consume geoJson representation using to_geojson and from_geojson functions.
Will I be able to pass a geoJson geometry and make it convert to a geometry type? The solution proposed for casting inputs would be great...

[steve-chavez]

Hey @massimiliano-cannata, can you see if https://github.com/PostgREST/postgrest-docs/issues/273#issue-519448273 helps?