Hoping for a little advice to set up infinite scroll 🙏

I am getting an infinite recursion

infinite recursion detected in policy for relation "conversation_users"

on inserting to conversation_users. I thought creating a security definer function would solve this, but it did not. Does anybody has an idea?

sql
CREATE OR REPLACE FUNCTION get_conversation_users_for_authenticated_user()
returns setof uuid
language sql
security definer
set search_path = public
stable
as $$
    SELECT DISTINCT user_id FROM conversation_users WHERE conversation_id IN (
        SELECT DISTINCT conversation_id FROM conversation_users WHERE user_id = auth.uid()
    )
$$;

CREATE POLICY "Can select conversation_users" ON public.conversation_users AS PERMISSIVE FOR
SELECT TO public USING (
    user_id IN (
        SELECT get_conversation_users_for_authenticated_user()
    )
);

CREATE POLICY "Can update to conversation_users" ON public.conversation_users AS PERMISSIVE FOR
UPDATE To public USING (
    conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid())
) WITH CHECK (
    conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid())
);

CREATE POLICY "Can insert to conversation_users" ON public.conversation_users AS PERMISSIVE FOR
INSERT To public WITH CHECK (
    conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid())
);

CREATE POLICY "Can delete from conversation_users" ON public.conversation_users AS PERMISSIVE FOR
DELETE TO public USING (
    (conversation_id IN (SELECT id FROM conversations WHERE admin_id = auth.uid()))
    OR (user_id = auth.uid())
);

infinite recursion

sql jwt testing

Does psql's

to_tsvector()

cache the document (table?) for some period? I have the below query. I ran it and correctly got 3 results. I then updated one of the rows to set

closed = true

and correctly got 2 results. I then updated that row back to

closed = false

and still only get 2 results??

sql
select *
  from public.jobs j
  where j.closed = false
    and j.valid_until > now()
    and to_tsvector(j.business_name) || ' ' ||
    array_to_tsvector(j.categories)
    @@ plainto_tsquery('demand');

If I drop the

j.closed

and

j.valid_until

checks it still only gives 2 results... hmmm

That specific row just won't turn up anymore

Super wierd, it's like that record doesn't exist if I use

to_tsvector

fml needed to

coalesce

a field

Query foreign table

Hey everyone, I'm fairly new to supabase and databases in general. I'm trying to build a simple news site where each article has multiple entities mentioned. For example "Google and snapchat sign an agreement" would have two entities Google and Snapchat.

I was wondering how to build and structure the tables so that I can see all articles for an entity

but also see all entities on an article

I started with something like this where the articles entities array had every detail on entities ... but this definitely doesnt sound like the right aproach

Here's an example of what I mean

Database structure for multiple relationship table

Hey guys. How do I push into an array using supabase's JS client? Would this be with the "update" method?

thanks

I guess to give more insight. This is what the column is set up as

did I do this correctly lol?

I want it to be an array that holds IDs (text)

meant to say strings mb

Looks correct then, yes.

ok

so right now those are empty when the user creates their account. Say they like something and I insert into the column, how would I update the column so it just pushes a new value to the array once it happens again?

oh wait im stupid i figured it out

👌 🤣

Is there a good way to test psql functions directly from the supabase ui?

Testing psql Functions