Thanks! I'll look into this!

Hey folks, I'm trying to write a plpgsql function which returns a subset of a table. For example a table that has columns A, B, C. I am trying to return the results of a SELECT A, B WHERE C = 'someconstant' Second to that I'm wanting to add a row to the result that doesn't exist in the databast as part of that return value. Is this possible?

Im having some problems writing a Row Level Policy for the following: I have a

users

table. A user has an

id

field (foreign key on

auth.oid()

) and also a

company_id

I want to write a RLP such that a user can select the row for any other user provided they are in the same company (i.e have the same

company_id

) I wrote the following statement, but the problem is that I get an infinite recursion error (which makes sense). Anyone know how I would be able to achieve my desired RLP?

(id = uid()) OR (company_id = ( SELECT users_1.company_id
   FROM users users_1
  WHERE (users_1.id = uid())
 LIMIT 1)))

I was able to figure it out: I put the above sql code in a function and then used the advanced settings to set the security to DEFINER instead of INVOKER

Is this the best way? Is there a better way?

I was able to figure it out I put the

what's the output SQL for this

supabase.from('cart').select('*, items:cart_item(*)')

?

How could I sync

auth.last_sign_in_at

with

public.user_details

?

Assuming you mean auth.users.last... you would put a trigger on update to auth.users table and check if new.last_sign_in_at is different than old.last_sign_in_at and update your public.user_details if it is.

Yes! Could I just trigger on every auth table update and just update it anyways?

Found this example for copying the id on every new creation of a user. But how do I update values on update of a user?

sql
create function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Is there a right way to format SQL? I've seen no consistency between formatters or guidelines. For example, I see these two all of the time:

sql
select first_name,
       last_name
from users

sql
select 
       first_name,
       last_name
from users

Is there a right way to format SQL

My plv8 function says that it can't serialize a bigint when I try to count the number of relations in a table. How can I serialize this without getting this error thrown at me?

hey guys, I need some help, I've setup a schema with RLS, but I'm getting this:

json
{
  "message": "new row violates row-level security policy for table \"families\"",
  "code": "42501",
  "details": null,
  "hint": null
}

Only when I create an item using

Prefer return=representation

to return the created object, if I perform the POST request without it, it works. I suppose it's a lack of permissions for selecting. Here is the whole code (https://gist.github.com/grsouza/4d6d0fc25fd69156e2789fe52eafa7ef)

insert RLS

So let's say I would like to write a function called

sum_values

that sums the values in a row and then puts them at the end. Let's say I have a table that looks like this:

sql
id   value_1   value_2   result
1     600        300       0
2     400        100       0
3     100        200       0
-- etc.

The end result should look like this:

sql
id   value_1   value_2   result
1     600        300      900
2     400        100      500
3     100        200      300
-- etc.

It could be done in

sql

or

plpgsql

, I guess.

sql
create or replace function sum_values() returns void as $$
  -- 1. Select value_1 and value_2 for each row
  -- 2. Into each result insert the proper sum
$$ language sql; -- or plpgsql at this point

As a bonus, how could I make it accept the argument of a range and sum the values in that given range? Or for specific ids only? Thanks in advance, everyone!

So let s say I would like to write a

Hi all, thanks in advance if you are able to help ! ⭐ I wish to execute this statement and trying to find how I can do it with the api

sql
SELECT *
FROM public.articles AS articles
WHERE EXISTS(
  SELECT FROM public.articles_filters 
  WHERE public.articles.id = article_id AND filter_id = 'bfc8c9ca-361c-51d5-be59-e2ce0aa7e6a6'
)

if I use

javascript
.select(`*, articles_filters!inner(*)`)
.eq('articles_filters.filter_id', 'bfc8c9ca-361c-51d5-be59-e2ce0aa7e6a6')

and because of the inner join it only returns the articles with the one articles_filters, while I would like to return with all of them as you do with .select(

*, articles_filters(*)

) but with the ability to filter I tried .filter(), but it seems that I still need to use !inner Kind regards, Joe

Hi all thanks in advance if you are able

Hi, how can I make a column mandatory based on the value of another column on the same table?

You can do a check constraint on the column based on 1 or more other columns, or a before trigger with a function to check both columns for your requirements. Just google postgres and column constraint or before trigger function for ideas, or give more info on details.

ty @garyaustin, im gonna check that out

Hey guys, I need help optimizing my database schema: This query:

sql
begin;
explain (analyze,buffers,timing)
DELETE FROM "public"."Result" WHERE "public"."Result"."id" IN ('cl1iagqm0003109l8pp19c020','cl1ngg8yt066909mp1jbcjf2g','cl1prkyhb107609minrghucq8','cl2uvt3jo064409l7voketuxg','cl1xy5oby075409l42s25q8sc');
rollback;

Is logging:

Delete on "Result"  (cost=0.42..26.51 rows=0 width=0) (actual time=4.245..4.246 rows=0 loops=1)
   Buffers: shared hit=28 read=9 dirtied=4
   ->  Index Scan using "Result_pkey" on "Result"  (cost=0.42..26.51 rows=5 width=6) (actual time=0.058..4.113 rows=5 loops=1)
         Index Cond: (id = ANY ('{cl1iagqm0003109l8pp19c020,cl1ngg8yt066909mp1jbcjf2g,cl1prkyhb107609minrghucq8,cl2uvt3jo064409l7voketuxg,cl1xy5oby075409l42s25q8sc}'::text[]))
         Buffers: shared hit=14 read=9
 Planning:
   Buffers: shared hit=56
 Planning Time: 0.425 ms
 Trigger for constraint Answer_resultId_fkey: time=6.694 calls=5
 Trigger for constraint Log_resultId_fkey: time=632.656 calls=5
 Execution Time: 643.656 ms
(11 rows)

So cascades are triggered for

Answer

and

Log

but for some reason, the

Log

trigger takes a ton of time but there are no

Logs

to delete in this case Any idea how I can optimize that?

I have a

select

looking like this:

.select('uid, created_at, text, topic, corrected, option, progress, characters')

Sometimes, the column

text

can be several thousands words long. Since I want to store the result of this query in my vue store, i don't want to have huge

text

values. Is it possible to adjust the query that the value of

text

is only e.g. 45 characters long?

You could create a view with it limited. You could use rpc and postgres function. You could just trim it right after the supabase call before storing it. Edit I guess you could also have a calculated column in your table that is 45 chars but that would add a bit to your table size.

Thanks gary. I guess rpc postgres would be the best choice so I don't need to pull extra load from the db or have redundant data in my db.

Hi all, am I able to access the storage table? eg:

sql 
.select(`*, storage.objects(*)`)

or is it out of reach from the api? I would like to avoid duplicating the metadata, but not too worried.

See here https://discord.com/channels/839993398554656828/989915552107614269/989917304844341259