Início / user-115061

serge1peshcoff's questions

Martin Hope
serge1peshcoff
Asked: 2017-02-27 06:17:14 +0800 CST
  • 5

Eu tenho esta tabela:

CREATE TABLE accounts (
  id BIGINT NOT NULL UNIQUE,
  balance INTEGER DEFAULT 0
);

E preciso atualizar o saldo de cada usuário após inserir linhas em 2 tabelas diferentes transferse deposits.

Criei 2 funções para calcular novos saldos de usuários e atualizá-los:

/* function for getting user balance */
CREATE FUNCTION get_balance(bigint) RETURNS bigint
    AS 'SELECT (
  SELECT COALESCE(sum(CASE WHEN won THEN amount * (multiplier - 1) ELSE -amount END), 0)
  FROM transfers
  WHERE user_id = $1
) + (
  SELECT COALESCE(sum(amount), 0)
  FROM deposits
  WHERE user_id = $1
) as sum;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

/* function for updating user balance */
CREATE FUNCTION upsert_balance(bigint) RETURNS VOID
    AS 'INSERT INTO ACCOUNTS (id, balance)
    VALUES ($1, get_balance($1))
  ON CONFLICT (id) DO
  UPDATE SET balance = get_balance($1);'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Mas não tenho certeza de como usar os valores recém-inseridos em um retorno de chamada de gatilho, porque preciso passar o idcomo um argumento para a upsert_balancefunção.

Como posso fazer isso?

EDIT: Eu consertei, graças à resposta de Adam, aqui está a versão de trabalho, caso alguém precise:

CREATE FUNCTION upsert_balance() RETURNS trigger AS $trigger_bound$
BEGIN
    INSERT INTO ACCOUNTS (id, balance)
    VALUES (NEW.user_id, get_balance(NEW.user_id))
    ON CONFLICT (id) DO
    UPDATE SET balance = get_balance(NEW.user_id);

    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER update_balance_on_inserting_transfer
AFTER INSERT OR UPDATE ON transfers
FOR EACH ROW
EXECUTE PROCEDURE upsert_balance();
postgresql trigger
Martin Hope
serge1peshcoff
Asked: 2017-01-16 06:11:24 +0800 CST
  • 26

Estou usando o PostgreSQL como meu banco de dados. E eu preciso criar uma entrada no banco de dados, e caso já exista, basta atualizar seus campos, mas um dos campos deve ser atualizado somente se não estiver definido.

Eu usei informações desta pergunta: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null , está bastante relacionado ao que eu tenho.

Tentei usar esta consulta, mas quando a executo, dá erro com Column reference 'affiliate_code' is ambiguous:

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(affiliate_code, value3);

(os valores reais são substituídos, é claro).

Se eu substituir affiliate_code = COALESCE(affiliate_code, value3)por affiliate_code = value3, tudo funciona, mas não da maneira que eu quero que funcione.

Como posso fazer isso funcionar?

Aqui está como minha tabela é definida:

CREATE TABLE accounts (
  id VARCHAR NOT NULL UNIQUE,
  token VARCHAR NOT NULL,
  affiliate_code VARCHAR
);
postgresql upsert