I'm trying to create this query in SqlDelight ```...

# squarelibraries

[Odinakachukwu Omaka-chukwu]

I'm trying to create this query in SqlDelight

create:
INSERT INTO user_account(username, password) 
VALUES (:username, crypt(:password, gen_salt('bf', 8)));

but I'm getting this error
Cannot bind unknown types or null

I've installed the required extensions and the function works without parameters, but it seems like it cant infer the password type. How do I go about this

[Odinakachukwu Omaka-chukwu]

Ended up using this

create:
INSERT INTO user_account(username, password)
VALUES (:username, crypt(COALESCE(:password,''), gen_salt('bf', 8)));

[griffio]

Assuming this is Postgresql as looks like pgcrypto extension functions
SqlDelight doesn’t know about extensions functions that you add manually as there are too many
You can see here for an example https://github.com/griffio/sqldelight-custom-dialect of how to add functions
In your case, if need it, you can add to the custom resolver - if you have it working somehow already then it’s probably too much bother

"crypt" -> IntermediateType(PrimitiveType.TEXT)
  "gen_salt" -> IntermediateType(PrimitiveType.TEXT)

Your .sq file would look like

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS user_account (username TEXT, password TEXT);

create:
INSERT INTO user_account(username, password)
VALUES (:username, crypt(:password, gen_salt('bf', 8)));

[Odinakachukwu Omaka-chukwu]

This is exactly what I needed, I might keep it as it is though, since I'm not calling the functions a lot. Thanks.