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: account
CREATE TABLE account (
    account_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    account_type VARCHAR(20), -- can be 'user', 'admin', or 'superadmin'
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    admin_id INT,
    FOREIGN KEY (admin_id) REFERENCES account (account_id) ON DELETE SET NULL
);

-- For bulk update
CREATE TYPE account_update AS (
    account_id INT,
    username VARCHAR(50),
    password_hash TEXT,
    email VARCHAR(100),
    account_type VARCHAR(20),
    is_active BOOLEAN,
    admin_id INT
);

-- For bulk update
CREATE OR REPLACE FUNCTION bulk_update_accounts(admin_id INT, accounts account_update[])
RETURNS VOID AS $$
BEGIN
    UPDATE 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
    WHERE a.account_id = u.account_id
      AND a.admin_id = admin_id;
END;
$$ LANGUAGE plpgsql;

Now in account.sql repo for sqlc:

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

This generates this code-

const bulkUpdateAccounts = `-- name: BulkUpdateAccounts :exec
SELECT bulk_update_accounts($1, $2)
`

type BulkUpdateAccountsParams struct {
	AdminID  int32  `json:"adminId"`
	Accounts string `json:"accounts"` // Account is of string type instead of a slice of struct
}

func (q *Queries) BulkUpdateAccounts(ctx context.Context, arg BulkUpdateAccountsParams) error {
	_, err := q.db.Exec(ctx, bulkUpdateAccounts, arg.AdminID, arg.Accounts)
	return err
}

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 :exec
UPDATE 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
WHERE
a.account_id = u.account_id
AND a.admin_id = sqlc.arg('admin_id');