https://supabase.com/ logo
Join Discord
Powered by
# sql
  • t
    found it. thanks! makes sense
  • m
    Hi all. Help how can I prohibit changing a specific cell with Row Level Security. For example, I can restrict access to other people's records like this:
    Copy code
    create policy "Can update own user data." on users for update using (auth.uid() = id);
    Question - how can I additionally forbid a user to change balance(num) cell in users_data table by using policy?
  • c
    Assuming that by "cell" you mean "column" - you cannot do that using RLS but you can do that in other ways, search for a thread called "Column Level Security", there are examples there
  • n
    I try to create a trigger which executes a function every time a file gets uploaded in a specific storage bucket I tried this (without 
    table_name
    too) but it doesn't work. Can somebody help me (ping appreciated)
  • g
    @User You need NEW. in place of table_name. This looks at the inserted data in the request.
  • n
    new error 🤔
  • g
    https://www.postgresql.org/docs/13/sql-createtrigger.html If someone else does not respond you'll have to read thru that. I do see some stuff on limitations of NEW/OLD for certain types of triggers, but I personally have only used column stuff in functions.
  • n
    just added 
    for each row
    , went through without errors but i have to check if it actually checks the bucket_id now
  • n
    thank you very much
  • n
    okayyy i think it works, awesome
  • n
    never really used sql, i'm more used to nosql/ORM, but supabase convinced me to try it
  • n
    it's me again is it possible to rename a file in a storage bucket through sql? I upload a file through the JS API to "{user_id}/upload.png", I can't assign it a name yet because the ID gets generated when i insert a new row in the database, which happens in the function executed by the trigger. I found out that the path is saved in 
    path_tokens
    , so my approach was to modify it in the function.
    Copy code
    sql
create or replace function
  public.create_post_on_bucket_upload()
  returns trigger as
  $$
  declare
    post_id text;
  begin
    insert into public.posts (user_id)
    values (
      auth.uid()
    )
    returning id into post_id;
    new.path_tokens[2] = post_id;
    return new;
  end;
  $$ language plpgsql security definer;
    it creates the function and I can run the upload process on my website, but the file name/location doesn't change. What did I do wrong?
    s
    • 2
    • 4
  • s
    Rename a file in storage bucket through SQL
  • j
    whats the limit on number size for serial ids?
    t
    • 2
    • 1
  • s
    I've made a plpgsql function to get the total number of jobs that meet a simple criteria. This works some of the time... Sometimes instead it returns null? Am I doing this wrong?
    Copy code
    sql
create or replace function count_open_jobs(out job_count integer)
returns integer
language plpgsql
as $$
begin
  select count(*) into job_count
  from public.jobs
  where valid_until > now()
    and closed = false;
end; $$
    In my code I have
    Copy code
    js
// in my db.js file, call the plpgsql function
export const countOpenJobs = async () => {
  return supabase.rpc('count_open_jobs');
};

// this bit is in my SvelteKit endpoint
const result = await countOpenJobs();
const count = result.data['job_count'];
    Example response when it works is
    Copy code
    json
{
  "error": null,     
  "data": {
    "job_count": 94  
  },
  "count": null,     
  "status": 200,     
  "statusText": "OK",
  "body": {
    "job_count": 94
  }
}
  • s
    I really don't understand. It's working consistently now with no change
  • s
    Was there an issue on supabase's end?
  • c
    Sorry for side-stepping your question (not sure what the problem might be), but out of curiosity - why are you using a function instead of a view for this? Seems like it will be both shorter and simpler, no?
  • s
    'cause I'm a monkey with a hammer
  • s
    Haven't done a view before
  • c
    ok 🙂 I think it will be something like this:
  • c
    Copy code
    CREATE VIEW public.count_open_jobs AS
SELECT count(*)
FROM public.jobs
WHERE valid_until > now() AND close = false;

-- Then you call it like this (or using the supabase client as if querying a table)
SELECT * from public.count_open_jobs;
  • c
    the IMPORTANT thing to keep in mind is security, of course - views are always executed as the user that defined them while functions can be executed both as the user who defined them OR the user who invokes them (depending on how you defined the function)
  • s
    Ok damn that is way easier
  • s
    TYVM
  • s
    Probably a few other things I could move from function -> view for
  • c
    it's possible...btw, keep in mind that the supabase client also supports returning the count for something (so you might not even need the view). The syntax is a bit weird, though, it's something like this:
  • c
    Copy code
    supabase.from('jobs')
      .select(undefined, { head: true, count: 'exact' })
      .eq('close', false)
      .gt('valid_until', now());
  • s
    I couldn't get the built in one working with the now() check
  • s
    😦
1...242526...52Latest