https://supabase.com/ logo
#sql
Title
# sql
a

AmusedGrape

03/18/2022, 1:40 PM
I'm getting this error:
syntax error at or near "insert"
Copy code
sql
create function commands_inc (x int, row_id text) 
returns void as
$$
  insert into command_logs (id, commands) values (row_id, x) on conflict do update
$$ 
language plpgsql volatile;
what am i doing wrong? thanks!
a

Avin

03/18/2022, 1:52 PM
For plpgsql you need,
BEGIN
and
END
statements.
But you can write this in
sql
as
Copy code
create function commands_inc (x int, row_id text) 
returns void as
$$
  insert into command_logs (id, commands) values (row_id, x) on conflict do update set commands = commands + x;
$$ 
language sql volatile;
You might need a
where
clause for the do update, also.
a

AmusedGrape

03/18/2022, 2:01 PM
@User i updated my code and schema a bit since i realized i needed some more data,
Copy code
sql
create function commands_inc (x int, row_id text) 
returns void as
$$
  if (select * from bot_analytics where timestamp > now() - interval '1 week') then
    update bot_analytics set commands = commands + x where bot = row_id
  else
    insert into bot_analytics (commands, bot) values (x, row_id)
  end
$$ 
language plpgsql volatile;
now i get syntax error on
if
a

Avin

03/18/2022, 2:03 PM
right, yeah then it's this issue, 'For plpgsql you need, BEGIN and END statements.'
a

AmusedGrape

03/18/2022, 2:03 PM
ah ok so like this?
Copy code
sql
create function commands_inc (x int, row_id text) 
returns void as
$$
BEGIN
  if (select * from bot_analytics where timestamp > now() - interval '1 week') then
    update bot_analytics set commands = commands + x where bot = row_id;
  else
    insert into bot_analytics (commands, bot) values (x, row_id);
  end;
END;
$$ 
language plpgsql volatile;
or in the if statements?
a

Avin

03/18/2022, 2:04 PM
yes, like that. also to end the if you have to use
end if
a

AmusedGrape

03/18/2022, 2:05 PM
oh perfect, yeah that worked, thanks!