Given a table
CREATE TABLE balances
(
username varchar(255) NOT NULL,
currency varchar(255) NOT NULL,
balance numeric NULL,
CONSTRAINT balances_pkey PRIMARY KEY (username, currency)
);
After attempting
CREATE OR REPLACE FUNCTION merge_balance(username varchar(255), currency varchar(255), to_add numeric) RETURNS void
AS $$ BEGIN
INSERT INTO balances(username, currency, to_add)
ON CONFLICT balances_pkey DO UPDATE SET balance = OLD.balance + to_add
END; $$ LANGUAGE plpgsql;
I get
ERROR: syntax error at or near "ON"
My PostgreSQL version (running in a container postgres:11.5)
SELECT version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.5 (Debian 11.5-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
Those ;,$ things are so annoying and now I get this one =_=
Has anybody solved such a problem? Thanks!
Consider:
create or replace function merge_balance(
p_username varchar(255),
p_currency varchar(255),
p_to_add numeric
) returns void
as $$
begin
insert into balances (username, currency, balance)
values(p_username, p_currency, p_to_add)
on conflict (username, currency) do update set balance = old.balance + p_to_add;
end; $$ language plpgsql;
Rationale:
you need a VALUES() clause to enumerate the values to insert; it is also a good practice to surround list the target columns - for this, you would better not have function arguments that have the same name as the table columns
the conflict target must be surrounded with parentheses - you can use a constraint name, but I find it is clearer to use the column names
Shouldn't it be
INSERT INTO balances VALUES(username, currency, to_add)
ON CONFLICT balances_pkey DO UPDATE SET balance = OLD.balance + to_add
You cannot use "old" as you are attempting. What you are doing is referencing the pseudo row OLD generated by DML statements for triggers. Even if could reference it would be NULL as all old columns are null on insert. This is not what you want the result would be null. But you do need to alias the table to avoid ambiguity on the update clause. Also it is not necessary to specify limits on the function header. Try:
create or replace
function merge_balance(
p_username varchar
, p_currency varchar
, p_to_add numeric
)
returns void
language sql
as $$
insert into balances as bal (username, currency, balance)
values (p_username, p_currency, p_to_add)
on conflict (username, currency)
do update
set balance = bal.balance + excluded.balance;
$$;
See full example here.
As an after thought I guess you could use old.balance by using old as the table alias.
insert into balances as old (username, currency, balance) ...
But that doesn't seem like a good idea, at least to me.,