How to use a enum in CASE statement (operator) and cast the statement to the correct type?

[robertgroh]

Hi I am trying to build a UPDATE query with conditional updates/SETs, this works fine with more primitive/directly supported data types, but for enums I encounter an issue, which I can't solve.

Using this DDL:

CREATE TYPE registration_state AS ENUM ('Created', 'Active', 'Terminated');
CREATE TABLE IF NOT EXISTS registrations
(
    payment_number VARCHAR(16)        NOT NULL PRIMARY KEY,
    customer_id    TEXT               NULL,
    state          registration_state NOT NULL DEFAULT 'Created'::registration_state
);

This is the native (postgreSQL) query I am trying to build with go-jet:

WITH updated AS (
    UPDATE registrations
    SET (customer_id, state) = 
        (CASE WHEN state != 'Terminated' THEN 'ABCDEF' ELSE customer_id END,
         CASE WHEN state != 'Terminated' THEN 'Active' ELSE state END)
    WHERE payment_number = '123456'::text
    RETURNING payment_number, customer_id,  state
)
SELECT * FROM updated;

This is by go code:

	caseExprCustomerID := CAST(CASE().
		WHEN(Registrations.State.NOT_EQ(enum.RegistrationState.Terminated)).
		THEN(String(properties.CustomerID)).
		ELSE(Registrations.CustomerID)).AS_TEXT()

	stateCaseOperator := CASE().
		WHEN(Registrations.State.NOT_EQ(enum.RegistrationState.Terminated)).
		THEN(enum.RegistrationState.Active).
		ELSE(Registrations.State)

	stateCast := CAST(stateCaseOperator).AS_TEXT() // enum column, cast to ?????

	updateProperties := CTE("update_properties")
	withStmt := WITH(
		updateProperties.AS(
			Registrations.UPDATE().
				SET(
					Registrations.CustomerID.SET(caseExprCustomerID),
					Registrations.State.SET(stateCast),
				).
				WHERE(Registrations.PaymentNumber.EQ(String(paymentNumber))).
				RETURNING(Registrations.AllColumns),
		),
	)(
		SELECT(updateProperties.AllColumns()).
			FROM(updateProperties),
	)
	slog.Debug("withStmt registration sql query: ", slog.Any("query", withStmt.DebugSql()))

	registrationDto := Registrations{}
	err := withStmt.QueryContext(ctx, db.JetPool(), &registrationDto)

And of cause this results in an error:
ERROR: column "state" is of type registration_state but expression is of type text (SQLSTATE 42804)

But currently I have no idea, how to cast the CASE expression correctly to the enum type.

[go-jet]

Since both case branches return type of registration_state, you don't need to add SQL cast. All you have to do is satisfy go compiler, by wrapping stateCaseOperator to StringExp:

Registrations.State.SET(StringExp(stateCaseOperator)),

In case all branches do not return the same type, you can add the sql enum cast first and than sql builder wrapper cast:

stateCast := StringExp(CAST(stateCaseOperator).AS("<schema_name>.registration_state"))

[robertgroh]

Thank you for the fast response, your time and the great project.