poutingemoji
01/17/2022, 8:11 AMsql
create function increment (x int, row_id int)
returns void as
$$
update table_name
set field_name = field_name + x
where id = row_id
$$
language sql volatile;
js
const { data, error } = await supabase
.rpc('increment', { x: 1, row_id: 2 })
hey, ik i can increment a field like this but is there a way i can dynamically increment a field? like provide the column name through the rpc?tourdownunder
01/17/2022, 9:20 AMsql
create function increment (schema text, "table" text, "column" text, filter_column text,
filter_value text)
RETURNS VOID AS
$$
BEGIN
execute format('UPDATE %I.%I SET %I = %I + 1 WHERE %I = %L,
schema,
"table",
"column", "column",
filter_column, filter_value'
);
END;
$$ language plpgsql;
poutingemoji
01/17/2022, 9:21 AMtourdownunder
01/17/2022, 9:21 AMpoutingemoji
01/17/2022, 9:32 AMtourdownunder
01/17/2022, 9:42 AMsql
create or replace function increment (schema text, "table" text, "column" text, filter_column text,
filter_value text)
RETURNS VOID AS
$$
BEGIN
execute format('UPDATE %I.%I SET %I = %I + 1 WHERE %I = %L',
schema,
"table",
"column",
"column",
filter_column,
filter_value
);
END;
$$ language plpgsql;
create table increment_test (
id int,
price int
);
insert into increment_test (id, price)
values
(1, 67),
(6, 54);
-- public is just the default schema
select public.increment('public', 'increment_test', 'price', 'id', '6');
select * from increment_test
poutingemoji
01/17/2022, 9:43 AMtourdownunder
01/17/2022, 9:44 AMpoutingemoji
01/17/2022, 11:28 PMsql
create or replace function increment ("column" text, filter_value text)
RETURNS RECORD AS
$$
BEGIN
execute format('UPDATE public.user_cards SET %I = %I + 1 WHERE uuid = %L SET stat_pts = stat_pts - 1 WHERE uuid = %L',
"column",
"column",
filter_value
);
END;
$$ language plpgsql;
hey sorry to bother you again. i was trying to modify the function you gave me but supabase says i have a syntax error somewhere?tourdownunder
01/18/2022, 1:17 AMreturn query execute
I removed that to suite the void as you had as it was simpler.
Also if will need to have returning *
as the last part of the update statement and then that will return the contents of the row.sql
-- drop function increment (schema text, "table" text, "column" text, filter_column text, filter_value text) ;
create or replace function increment (schema text, "table" text, "column" text, filter_column text, filter_value text)
RETURNS record AS
$$
DECLARE rec record;
BEGIN
execute format('UPDATE %I.%I SET %I = %I + 1 WHERE %I::text = $1 returning *',
schema,
"table",
"column",
"column",
filter_column
)
using filter_value into rec;
return rec;
END;
$$ language plpgsql;
create table increment_test (
id int,
price int
);
insert into increment_test (id, price)
values
(1, 67),
(6, 54);
-- public is just the default schema
select public.increment('public', 'increment_test', 'price', 'id', '6');
select * from increment_test