Postgres Store Procedure

[skedee]

Does sqlc have support to generate code for migrations that contain stored procedures?

[5hubham5ingh]

@skedee Did you find the answer?

[kyleconroy]

@skedee I don't think I can answer your question without more information. Can you provide a full example?

[5hubham5ingh]

@kyleconroy Hi, let me give an example.

migration.sql

-- Table: accountCREATETABLEaccount ( account_id SERIALPRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash TEXTNOT NULL, email VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, account_type VARCHAR(20), -- can be 'user', 'admin', or 'superadmin' is_active BOOLEANNOT NULL DEFAULT TRUE, admin_id INT, FOREIGN KEY (admin_id) REFERENCES account (account_id) ON DELETESETNULL ); -- For bulk updateCREATETYPEaccount_updateAS ( account_id INT, username VARCHAR(50), password_hash TEXT, email VARCHAR(100), account_type VARCHAR(20), is_active BOOLEAN, admin_id INT ); -- For bulk updateCREATE OR REPLACEFUNCTIONbulk_update_accounts(admin_id INT, accounts account_update[]) RETURNS VOID AS $$ BEGINUPDATE account AS a SET username = COALESCE(u.username, a.username), password_hash = COALESCE(u.password_hash, a.password_hash), email = COALESCE(u.email, a.email), account_type = COALESCE(u.account_type, a.account_type), is_active = COALESCE(u.is_active, a.is_active) FROM UNNEST(accounts) AS u WHEREa.account_id=u.account_idANDa.admin_id= admin_id; END; $$ LANGUAGE plpgsql;

Now in account.sql repo for sqlc:

-- name: BulkUpdateAccounts :execSELECT bulk_update_accounts($1, $2);

This generates this code-

constbulkUpdateAccounts=`-- name: BulkUpdateAccounts :execSELECT bulk_update_accounts($1, $2)`typeBulkUpdateAccountsParamsstruct{AdminIDint32`json:"adminId"`Accountsstring`json:"accounts"`// Account is of string type instead of a slice of struct } func (q*Queries) BulkUpdateAccounts(ctx context.Context, argBulkUpdateAccountsParams) error{_, err:=q.db.Exec(ctx, bulkUpdateAccounts, arg.AdminID, arg.Accounts) returnerr }

See, the Accounts is of string type instead of a slice of struct.
Am I doing something wrong in my SQL definition, or does sqlc not yet support generating the correct Go type for a function parameter that is an array of a composite type?

Before using SP, I have tried this in the account.sql as well but it also has the same issue with generated type.

-- name: BulkUpdateAccounts :execUPDATE account AS a SET username = COALESCE(u.username, a.username), password_hash = COALESCE(u.password_hash, a.password_hash), email = COALESCE(u.email, a.email), account_type = COALESCE(u.account_type, a.account_type), is_active = COALESCE(u.is_active, a.is_active), admin_id = COALESCE(u.admin_id, a.admin_id) FROM UNNEST(sqlc.arg('accounts')::account_update[]) AS u WHEREa.account_id=u.account_idANDa.admin_id=sqlc.arg('admin_id');