Not fully compatible with PostGIS Geometry type

[mylaluna]

Version

1.14.0

What happened?

I have a very similar setup as an example from a closed issue: #1650

In this issue's SQL query section, it casts the $5 (location) to geometry type (a local type introduced by PostGIS)

INSERT INTO parties (
  id, 
  user_id,
  title,
  is_public,
  location,
  street_address,
  postal_code,
  state,
  country,
  start_date,
  end_date
) VALUES (
  $1, $2, $3, $4, ST_GeomFromEWKB($5::geometry), $6, $7, $8, $9, $10, $11
)
RETURNING *;

When using SQLC to generate the insert function, its parameter type struct will define the location field as geometry type instead of interface{}.

From there, if I run the insert function with location parameter being go-postgis.Point type, there will be an error message from PostgreSQL:

Error:      	Received unexpected error:
        	pq: invalid byte sequence for encoding "UTF8": 0x00

I find the solution to this is very simple: just remove the geometry casting from the query, ST_GeomFromEWKB($5).

As mentioned above, removing the casting will make SQLC unable to recognise the geometry type but use interface{} type. However, the insert function will run successful.

This looks like a bug to me.

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

No response

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

[eullerpereira94]

The function ST_GeomFromEWKB takes a bytea as an argument, so casting the $5 parameter to geometry doesn't works. The correct approach is to cast $5 to bytea, then sqlc will generate the proper code. Also, be aware the fact that ST_GeomFromEWKB is not part of the Postgres standard functions, and since the tool doesn't connect to a database to generate code, sqlc can't properly generate code to handle this case out of the box.