https://supabase.com/ logo
Join Discord
Powered by
# sql
  • m
    Okay. I will do that. Thanks for the help.
  • j
    Yeah sorry, just don’t know what types of arguments you require.
  • m
    I meant the same kind of arguments/parameters that one can give to database functions.
  • j
    Yeah, I guess I'm not ab to help you because I don't know what you are using the arguments for.
  • j
    is there way to see if condition for if value is type uuid because I have input from jsonb and have null check but then want if its not null but its also not a uuid like Basically if its not a uuid provided set it
    Copy code
    if (jsonb_typeof(jsonb_extract_path(child, 'id')) = 'null' or (not child->'id'::uuid)) then
        child = jsonb_set(child::jsonb,'{id}',to_jsonb(uuid_generate_v4()));
      end if;
    c
    • 2
    • 2
  • c
    Checking if string is UUID
  • b
    Hey friends, how can I rename the key in a JSONB list? My column 
    col
    for example has: 
    [{id: "block 1", steps: [...]}, {id: "block 2", steps: [...]}]
    , I'd like to rename all the 
    steps
    keys to 
    blocks
    on all rows. Here is what I found: https://stackoverflow.com/questions/50871692/how-to-rename-a-key-in-a-jsonb-list So, I tried:
    Copy code
    sql
update "Typebot" t1
set col = 
    (select json_agg(el::jsonb - 'steps' || jsonb_build_object('blocks', el->'steps')) 
     from "Typebot" t2, jsonb_array_elements(t2.col) as el
     where t1.id = t2.id)
    But it says: 
    function jsonb_array_elements(jsonb[]) does not exist
    t
    • 2
    • 4
  • t
    try to unnest 
    t2.col
    into just 
    jsonb
  • b
    How can I do that?
  • d
    Does Supabase support IN and OUT params in stored procedures? I'm not seeing the stored procedures I create pop up under the stored procedures sections under the API tab
    c
    • 2
    • 5
  • c
    Does Supabase support IN and OUT params
  • t
    postgres arrays
  • b
    Hey friends, I'm wondering why this query is invalid:
    Copy code
    sql
select w.id, (
        SELECT  COUNT(*)
        FROM    "MemberInWorkspace" m
        WHERE   w.id = m."workspaceId"
        ) AS count
from "Workspace" w
group by w.id
where count>2
    it says: 
    Failed to validate sql query: syntax error at or near "where"
  • j
    @baptisteArno I don’t think you can perform a query in the select part of the statement. It looks to me like you want a join
  • b
    Removing the last 
    where
    line, it works fine! I just don't understand why I can't add this last line
  • j
    @baptisteArno something in the back of my brain is telling me that the where should be before the group
  • j
    That looks like it might be
  • b
    no same error 😦
  • f
    @baptisteArno ey! morning! the where clause need to be placed under the from sentence
  • f
    if u need to filter the group use having then
  • f
    Copy code
    sql
SELECT
    customer_id,
    SUM (amount)
FROM
    payment
GROUP BY
    customer_id
HAVING
    SUM (amount) > 200;
  • f
    for ex
  • l
    Can anyone help me with this sql statment for a row level policy? I have a table called 
    Posts
    . A post is created by a 
    User
    and so has a 
    posts.user_id
    column. A 
    User
    also belongs to a 
    Company
    , so a user has a 
    users.company_id
    column I want to write a policy such that the authed user can SELECT any post created by any user in his company. I have the following SQL statement:
    Copy code
    CASE
    WHEN (( SELECT u.company_id
       FROM users u
      WHERE (u.id = posts.user_id)
     LIMIT 1) = ( SELECT v.company_id
       FROM users v
      WHERE (v.id = uid())
     LIMIT 1)) THEN true
    ELSE false

END
    Its not working for me, I suspect because the syntax is wrong (I've never really written statements that return booleans)
  • l
    On a related note: Is there a way to debug to see exaclty what was executed and returned for the RLP when I made a query? I had a look at the postgres logs, but they are a bit cryptic
  • l
    to be honest, knowing how to debug this would be more useful, since I realize now there are multiple RLP (e.g. each table has its own SELECT RLP), so the error could even be there
  • m
    Please note that RLS is essentially a where clause. The way you are looking to do this will call for a JOIN statement for reading on every post. I don't know if that's desirable as it would come with costly queries. Perhaps you can add a 
    company_id
    in the posts table as well.
  • l
    yeah thats a good point. Perhaps I'll do that, it'll simplify things at the cost of some duplication. Do you have any tips on debugging?
  • m
    For me, the easiest way was always to check the queries here: https://app.supabase.com/project/_/database/postgres-logs
  • l
    I see. I dont find that particularly helpful as I dont see a way to view the exact values used or output generated. But thank you for answering my questions @mansueli!
  • m
    You can combine it with a PSQL connection and execute the query there as well
1...454647...52Latest