Hello everybody! Do you know if there's a way to ignore RLS in a database trigger?

Yes, use "security definer" in the function description.

It worked! Thanks!

I have the following query that I want to create a view for:

sql
select 
  sp.name, sp.sku, sp.id, 
  sup.display_name as supplier_name,
  ps.id is not null as is_pre_selected
from 
  source_products sp
left join 
  suppliers sup
on 
  sp.supplier_id = sup.id
left join 
  product_shop ps 
on 
  ps.product_id = sp.id and ps.shop_id = 'a50364d3-f61b-4b02-ad34-07cf7993aa38'
where 
  supplier_id = '157545b6-82b6-43e4-9d7d-f1f61cb49bab' 
limit 
  100

I want to abstract the 'hard-coded' values. The first question is, is that even possible with a view? If I understand views correctly I can append the where clause when doing the actual query, since it behaves like a normal table. But what about the

shop_id

in the second

on

condition? If at all possible I'd like to avoid creating a function.

If it's possible to create the query with supabase-js that would be even better

I ended up with this abomination here:

sql
create or replace function get_source_products(supplier_id_param uuid, shop_id_param uuid)
returns table (
  id uuid,
  main_sku text,
  name text,
  description text,
  sku text, 
  variation_count int2,
  properties jsonb,
  meta jsonb,
  list_price int4,
  wholesale_price int4,
  size text,
  color text,
  supplier_name text,
  is_pre_selected boolean
) as $$
begin
  return query 
    select 
      sp.id,
      sp.main_sku,
      sp.name,
      sp.description,
      sp.sku, 
      sp.variation_count,
      sp.product_properties,
      sp.product_meta,
      sp.list_price,
      sp.wholesale_price,
      sp.size,
      sp.color,
      sup.display_name,
      ps.id is not null
    from 
      source_products sp
    left join 
      suppliers sup 
    on 
      sp.supplier_id = sup.id
    left join 
      product_shop ps 
    on 
      ps.product_id = sp.id and ps.shop_id = shop_id_param
    where 
      sp.supplier_id = supplier_id_param;
end;
$$ language plpgsql;

I'm trying to return a join between 2 tables but it's saying the set of is not added?

Has anyone added a function that returns a new join record?

This thread might help you (not the details, but setof not working if you don't use a standard table). You will probably need to set things up with the sql editor though.

what thread?

Hi, I'm having trouble getting an SQL function to work with RPC, even though I was able to get a very similar function to work 🙂 I'm able to run this query successfully to create the function:

create or replace function add_rating_count_artist (artistid integer) 
returns void as
$$
  update artists
  set ratings = ratings + 1
  where id = artistid;
$$ 
language sql volatile;

Then in my app I call:

async function addRatingCountToArtist(artistId) {
    const { data, error } = await supabase.rpc( 'add_rating_count_artist', { artistid: artistId })
  }

When I check the db, the

ratings

value has not changed. FYI, the

artistId

I'm passing in is a number. table name is

artists

,

id

is an

int4

,

ratings

is an

int4

. Any ideas? Thank you

Hey all, struggling to create a function trigger that updates the value of

email_confirmed_at

field in my own profile table whenever a user finishes sign-up and confirmation.

I'll just throw this out, but probably won't be around later to help... If you don't provide some detail on what you have tried, where your problem is and hopefully some preliminary code, you will only get this same response sometime during the night (or at least slow period here)..

Actually figured it out, and very sorry as I realized I did not provide anything useful to go on with

Did not realize that plpgsql allowed basically vanilla SQL statements, so I solved that issue by creating a function like:

begin
  UPDATE public.profile 
  SET confirmed_at = new.confirmed_at 
  WHERE id = new.id;

  return new;
end;

It seems like my organisation member is unable to view SQL snippets for Supabase DB I created in the dashboard. Can somebody help me?

Sharing SQL snippets

hey guys, sql noob here. need a function to sum a column

amount

of all rows in a table with specific columns (account and tokenId) my try looks like

create or replace function total_token_offers_by_account(tokenId varchar, account varchar) returns int8 as $$
  select count(amount) from "Offers" where owner = account and tokenId = tokenId
$$ language sql

when I call it via

select total_token_offers_by_account("1", "0x...")

it always throws like

column "1" does not exist

. Well it should look for column

tokenId

and then search for the value

"1"

.

crossposing from #843999948717555735 here since it might fit better; I'm trying to make a RLS policy where anyone can get rows from a table but only specific information is returned, here's what i have so far but its giving me a syntax error:

sql
CREATE POLICY "Public profiles with minimal data"
ON public.profiles
FOR SELECT USING (
  SELECT id, preferred_name, identity_data - 'email' identity_data, access FROM public.profiles 
);

postgres policy syntax error

Hello everyone ! I found a cool feature yesterday, the text array. I wanted to use it with a trigger function but the array_append function doesn't seems to work...

create function push_projectid_to_profile()
returns trigger as $$
begin
  update profiles
  set projects = array_append(projects, new.id)
  where id = new.creator;
  return new;
  end;
$$ language plpgsql security definer;

create trigger on_project_created
  after insert on projects
  for each row execute procedure push_projectid_to_profile();

I received this message from the server error "function array_append(text[], uuid) does not exist" The thing is, array_append should exist according to the doc. Did I do something wrong here? Thanks!

try casting uuid to text

array_append(projects, new.id::text)

Aaaaaaah it worked! Thank you very much 🥳

I'm trying to create an upsert function to call in the sql editor (I'm going to add more to it later). I'm getting a "Success. No rows returned." and I kinda need the row data client side.

create or replace function dummy() 
returns text
language plpgsql volatile
as $$
declare
  new_row text;
begin
  insert into slugs(slug, user_id)
  values('slug-test-val', 'user-id-test-val')
  on conflict(user_id) do update set slug = EXCLUDED.slug returning user_id into new_row;
  return (new_row);
end;
$$;

select * from dummy();

Anyone see what I'm missing?

i don't see anything wrong -- but if you're running this in the SQL editor -- make sure you only have

select * from dummy();

there. In other word, comment out the function or remove it from the editor so you can get an output. Apparently, if you have two statements, you won't get a table result.

Is there a way to mimic a logged in user to test RLS in psql?

Can't help much more than this thead i was in, but in debugging an issue this came up: SELECT set_config('request.jwt.claims', jsonb_build_object('role', 'authenticated')::jsonb::text, true)::text; from osaxma in this thread https://discord.com/channels/839993398554656828/939939463545651231 Should give you a clue. Probably can substitute uid with a real uid if you need to test an actual user.

I'll give that a shot. Thank you.