https://supabase.com/ logo
Join Discord
Powered by
# sql
  • s
    Yeah we don't have support for procedures atm @User, only functions
  • s
    So uh, is there a way to delete the procedure i made? 🙂
  • s
    DROP procedure name;
    worked. It no longer shows in my Functions list of the supabase ui 👍
  • s
    My function creates 3 records, 2 of which rely on each other (both have FK's that cannot be null). I'm guessing I've not created it correctly, because I'm now getting:
    Copy code
    sql
{"hint":null,"message":"null value in column \"invoice_id\" of relation \"jobs\" violates not-null constraint","code":"23502","details":"Failing row contains (...)."}
    Anyone willing to help me debug? I'll post the code in a thread cause it's relatively big
    j
    • 2
    • 22
  • q
    anyone do a write-only RLS policy? I'm trying to make a simple insert only policy and it seem to fail with
    Copy code
    new row violates row-level security policy for table
    b
    s
    +3
    • 6
    • 16
  • q
    I have RLS for the table on and a simple 
    WITH CHECK (true)
  • q
    When I also enable a select policy, it is working.
  • q
    (please @-me if you have advice!)
  • b
    Write only RLS
  • d
    Has anyone tried creating a storage bucket inside a trigger when a user is created? I'm seeing "permission denied for schema storage" for whatever reason when it's done within that context.
  • s
    Hi, i have a question for User handling. I want to have session with multiple Users joining in. These users could be anonymous with only a name. So my table create is:
    Copy code
    create table sessionUser (
  sessionID text not null,
  useruid text,
  username text not null
);
    Now i want the users to be able to add themself to a session. But this should only be allowed if the useruid that the users sends is the same as the logged in user OR if the useruid is empty this looks like this:
    Copy code
    create policy "Users can insert if loggedIn or anonymous"
  on sessionUser for insert
  with check ( (auth.uid() = useruid OR useruid == null)  );
    Now there is one Problem. I want to prevent a second user to join the session with a name that is allready used. But i cant make it unique, because in other sessions the name is allowed. How do i make a query in this check for usernames in relation to sessionIDs and disallow used usernames? Thanks
  • s
    You can make it unique by using a composite key, so you combine the username and sessionID, so that user is always unique per sessionID
  • s
    like this:
    Copy code
    create table sessionUser (
  sessionID text not null,
  useruid text,
  username text not null,
  primary key (sessionID, username)
);
  • s
    ?
  • s
    Thank you very much! This works great and i would never get this idea 😄 Thanks!
  • s
    Hi there friendly internet! Does anyone know if I can use "auth.uid()" within a view definition? I have the snippet
    Copy code
    sql
select distinct on (s.name)
  max(s.group_id) as group_id,
  s.name
from aggregate_settings as s
where s.group_id = 0 or s.group_id in (
  select
    ugr.group_id
  from
    user_group_relations as ugr
    where ugr.user_id = '1c851fa2-4838-4e28-a92b-8b33bc9d5aad'
)
group by s.name;
  • s
    It does exactly what I need, but I want to turn it into a view and use the 'auth.uid()' stored proceedure instead of a hardcoded uuid
  • s
    A view behaves like a table. As long as you're not using views inside views inside views (which could hurt performance), I don't see any reason why you wouldn't be able to do that.
  • z
    Hi everyone! I have a problem here, The Supabase Storage return the full path like 
    (public/products/feca4889.png)
     I stored this full path in a table like this: `` +----+---------+------------------------------+ | id | product | image | +----+---------+------------------------------+ | 1 | orange | public/products/feca4889.png | +----+---------+------------------------------+ `` Now! How i can retrieve the 
    bucket_id
    and the 
    name
    of my product image from the full path? like that:
    Copy code
    SQL
delete from storage.objects where bucket_id = 'public' and name = 'products/feca4889.png'
    Thanks!
  • z
    I found a solution To retrieve 
    bucket_id
    from the full path:
    Copy code
    SQL
select split_part('public/products/feca4889.png', '/', 1);
    To retrieve 
    name
    from the full path:
    Copy code
    SQL
select array_to_string((string_to_array('public/products/feca4889.png', '/'))[2:], '/');
  • z
    is this the only solution?
  • r
    In a policy, any ideas on how to reference a polymorphic ("commentable") object? I have a comments table with a "commentable_id" and a "commentable_type". I want my policy to limit commenting only on things that are within the scope of the user's team's things.
  • n
    Has anyone experience with pgaudit in supabase?
  • b
    can someone tell me why this sql statement
    Copy code
    ALTER TABLE public.profiles
ADD COLUMN email varchar references auth.users not null;
    returns this error 
    foreign key constraint "profiles_email_fkey" cannot be implemented
  • b
    Does this have to do with that I already have an 
    public.profiles
    field 
    id
    that references 
    auth.users
    field 
    id
    ?
  • b
    Swapped it out for this sql and it seems to have worked
    Copy code
    ALTER TABLE public.profiles
ADD COLUMN email varchar,
add constraint profiles_email_fkey
foreign key (email)
references auth.users (email)
  • s
    No its because you have rows filled out
  • s
    so when you add the column, the rows already in the table cant be null but dont have a default value
  • s
    so add a default to it, for the rows currently in there
  • b
    so had i removed the ‘not null’ it would have worked?
1...151617...52Latest