https://supabase.com/ logo
#help
Title
# help
p

poutingemoji

01/17/2022, 8:11 AM
Copy code
sql
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;
Copy code
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?
t

tourdownunder

01/17/2022, 9:20 AM
If I had to guess it it would be something like
Copy code
sql
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;
p

poutingemoji

01/17/2022, 9:21 AM
im not familiar with plpgsql, how would i call the rpc for this?
t

tourdownunder

01/17/2022, 9:21 AM
I missed the first line of the definition
Just edited now. I can test it in a bit.
p

poutingemoji

01/17/2022, 9:32 AM
yeah thatd be great
t

tourdownunder

01/17/2022, 9:42 AM
I wasn't too far off
Copy code
sql

 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
Now you should be able to run it like you were before though with the new params
p

poutingemoji

01/17/2022, 9:43 AM
ah ok, this is great thanks
t

tourdownunder

01/17/2022, 9:44 AM
no worries
p

poutingemoji

01/17/2022, 11:28 PM
Copy code
sql
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?
@User
t

tourdownunder

01/18/2022, 1:17 AM
You may need to
return 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.
I was a bit off again without testing slight modification of function required.
Copy code
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