Hey tried to create an rls policy for: Authenticated users can do everything (select, ...) to the product table when the shop specified with a shop_id contains the uid() in an array called authorized users. This doesn't work: (image) It gives me this error: Error adding policy: operator does not exist: uuid = uuid[]

This is also smth I have tried

(uid() IN (SELECT authorized_users FROM shops s where s.id = shop_id))

Supabase + Prisma & db users

array in rls

how do you declare a table type in plpgsql? I see %rowtype but if i want to

select * from todos where todo.done = true into sub_todos;

its just a filtered table but I dont think

declare
sub_todos public.todos%type;

is a thing. Or

declare
sub_todos public.todos%rowtype[];

table type in plpqsql

how can I combine the result of this two select? I'm new to postgrest and I only knew some basics of sql

I want to get the number of rows in user_post_comments by user_post_id, thank you

count

Is it possible? btw I don't have 'count' column name in any of may tables

[SOLVED]

is

input->'id'

and

json_extract_path(input, 'id')

the exacct same or is there difference?

1) I want to insert a new row in a table if it does not already exist. Otherwise, I want to update the same values I wanted to insert earlier. How would I go about it? 2) Is it wise to use dates as row IDs? I know my table will have one row for each date.

Here is an example of a query that will update on conflict (if it already exists). https://github.com/jensen/tictactoken/blob/1ad65c09c9f2ea032bc174d1e097dceb1d940de9/server/auth.ts#L55

If you are using the postgrest api, then you can do an

upsert()

. https://supabase.com/docs/reference/javascript/upsert

So

select into

always returns one row to the variable of %rowtype its not ever multiple even if multple rows pass where?

you can agg it into an array using

array_agg(s.var)

I cannot use this because I am creating a database function that needs to do this.

Ok, then my first link should be more in line with what you need.

Therefore, I have to use this. Can you have a look at this link: https://stackoverflow.com/a/6527838/18503001 This says that this approach is not recommended.

Are these both approaches the one in stackoverflow and yours the same?

There are a lot of examples in that stack overflow thread. As you can see I used

on conflict

and since my query is used to create a user record with a provider id, I use the provider id (from discord) as the conflict key.

It can really depend on the type of query you are doing.

I implemented this to serve a pretty specific purpose around auth providers.

Can I tell you my specific use case and you can let me know if this is the right direction?

Sure, I can give an opinion

So I have a table where my front end can "add" bank like financial transactions. I want to have another balances table where a new row is added whenever a new transaction is inserted into the transactions table. So, if I add ten transactions for today, there has to be only one row in the balances table with today's date (hence, each row with UNIQUE date column). And with each new transaction, the relevant column in the balance table will be updated according to the amount in the transaction row.

Ok. So you will have a trigger on the transaction insert and that will call a function that does the upsert?

Exactly

I will later on add functionality for updates and deletes as well but this will do for now.