Does anyone have good suggestions for debugging a plpgsql trigger? I'm not sure what's wrong with mine and I can't read the output if the trigger just fails

If anyone wants to take a looksie though, here is my code

sql
create or replace function link_cosmos_userid() 
returns trigger 
language plpgsql 

as $$
begin
  update auth.users
  set raw_user_meta_data = jsonb_set(raw_user_meta_data, '{cosmos_userid}', get_cosmos_userid(new.email), true)
  where users.id = new.id
end;
$$;

Failing trigger

Hi everyone! I am trying to create a function however I am having some trouble getting it to return anything. When I call

select fetch_data()

it says "structure of query does not match function result type"

create or replace function fetch_data()
  returns setof "SolanaFloorTracker"
  language plpgsql
  as $$
    begin
    return query
      SELECT rank_filter.* 
      FROM (
        SELECT "SolanaFloorTracker".*, 
        rank() OVER (
            PARTITION BY "CollectionName"
            ORDER BY created_at DESC
        )
        FROM "SolanaFloorTracker"
      ) rank_filter WHERE RANK <=2;
    end;
  $$

I also tried

returns setof record

but the output is set-valued function called in context that cannot accept a set. I tested the inner SQL query and it works, so I think I just need to learn about what I need to return to get this to work properly?

I was helping @User on this in another thread, and we got the actual query to work, but because the total query process adds a "rank" column to the result set, the original setof "table" won't work anymore. It is beyond my skill set to figure out if it needs to return "record" and define the values, or a "table" with (column types) and the correct "grammar" to do it.

return setof with extra column

sql
    SELECT 
    g.user_id,
    (select curRank+1 into curRank) rank1
    FROM 
    (
        SELECT 
        p.user_id
        FROM posts p
        WHERE p.parent_uuid = input_uuid
        OR p.uuid = input_uuid

        GROUP BY p.user_id
    ) g

SELECT ... INTO is not allowed here

curRank is the issue here, any ideas 🤔 ?

I come from a background of Firebase, and some RoR. I want to learn how to write SQL queries like this.

CREATE TABLE posts (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users not null,
  user_email text,
  title text,
  content text,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);

alter table posts enable row level security;

create policy "Individuals can create posts." on posts for
    insert with check (auth.uid() = user_id);

create policy "Individuals can update their own posts." on posts for
    update using (auth.uid() = user_id);

create policy "Individuals can delete their own posts." on posts for
    delete using (auth.uid() = user_id);

create policy "Posts are public." on posts for
    select using (true);

I need a straight-to-the-point guide to the query formats for Supabase. I wasn't able to figure out what to look for. Thanks.

Besides specific features for auth functions, Supabase is straight up postgresql and you can use that as resource for writing SQL, functions etc. The documentation on the Supabase site covers api and javascript query formats (and the UI actually generates some API examples for your tables). You can create simple tables with the UI and set their RLS policies, or you can do SQL like you show to set up or modify tables directly in the UI sql editor, or you can tie in your own editor if desired. Realtime and Storage of course are more Supabase specific, but those are mainly API level issues and policies for protection of the files.

Thank you for clarifying the fact that this is just plain SQL. I might have looked at different formatting or something cause I was quickly thrown off. Oh, and the bracket notation seemed different from beginner queries, but now I realize I was comparing create commands to basic query commands. > You can create simple tables with I just want to make sure I'm understanding this correctly, but I am not sure what you mean by simple here because I don't have contextual knowledge. How simple are we talking? Also, do you mean, that more complex schemas can't be done with the UI or the SQL editor? Thank you again. I appreciate your time and knowledge.

Simple just means they only have a subset of possible datatypes and options in the table UI. Indexes have to be done with SQL if you need them for performance. The SQL editor in the UI will pretty much let you do anything. You can also set up a table in the table editor UI and then add to it with alter statements if need be.

Alright! I think I understand now.

Hi all 👋 I'm trying to update the related values on a table. table TH has a link with table TR and I would hope to be able to modify the associations with the following query:

const { data, error } = await supabaseClient
      .from('treasure_hunt')
      .update({
        trigger: triggerIds,
      })
      .eq('uuid', id);

but it doesnt work as expected. Instead I am just "brute forcing it" by deleting existing links and recreating the new links

await supabaseClient
      .from<definitions['trigger_treasure_hunt_link']>(
        'trigger_treasure_hunt_link'
      )
      .delete()
      .match({ treasure_hunt_uuid: id });

    const { data, error } = await supabaseClient
      .from('trigger_treasure_hunt_link')
      .insert(
        triggerIds.map((trigger) => ({
          treasure_hunt_uuid: id,
          trigger_uuid: trigger,
        }))
      );

Is there a smart way to do this like query 1? Thanks

update foreign key

How do I grant update access for a specific column, for example, I want the user to be able to update their display name, but not some of their other metadata

Column Protection

Hi all, I'm (still) having a little trouble with some logic that works just fine when executed stand-alone, but fails when executed within a trigger. I'm trying to update some data on a newly created user automatically to link our old account system with Supabase. This is what works fine:

sql
update auth.users
set raw_user_meta_data = jsonb_set('{}', '{cosmos_userid}', public.get_cosmos_userid(email))
where email = 'my_private@email.address'
returning email, raw_user_meta_data;

but this does not:

sql
create or replace function public.link_cosmos_userid() 
returns trigger 
language plpgsql 
as $$
begin
  update auth.users
  set raw_user_meta_data = jsonb_set('{}', '{cosmos_userid}', public.get_cosmos_userid(new.email))
  where id = new.id;
  return new;
end;
$$;

The problem seems to be in the stored procedure call

get_cosmos_userid()

. Even if I hardcode the same email address, I get some arcane error that is useless. If I replace the function call with something like

to_jsonb('test'::text)

, the trigger works just fine. But that same function call works just fine when executed outside of the trigger. Anyone have any idea what the problem might be? Perhaps some incompatibility with SQL and PLPGSQL?

For reference, the error is something like this (abbreviated):

execute 3: with recursive pks_fks as ( -- pk + fk referencing col select conrelid as resorigtbl, unnest(conkey) as resorigcol from pg_constraint where contype IN ('p', 'f') union -- fk referenced col select confrelid, unnest(confkey) from pg_constraint where contype='f' ), views as ( select c.oid as view_id, n.nspname as view_schema, c.relname as view_name, r.ev_action as view_definition from pg_class c join pg_namespace n on n.oid = c.relnamespace join pg_rewrite r on r.ev_class = c.oid where c.relkind in ('v', 'm') and n.nspname = ANY($1 || $2) ), ...........

Trigger vs direct execution discrepancy

Hi, does anyone have experience using supabase for a multi-tenant architecture? can you guide me in the right direction

Dont return object but value when querying foreign table

Multi tenant implementation

Hello guys I have a new question for you! I want enable the extension of postgis during the "docker up" using an sql file (Im already using it to setup the schemas I need) From what I saw I can: add

CREATE EXTENSION IF NOT EXISTS postgis
    SCHEMA extensions
    VERSION "3.1.4";

To generate the schema then Im enable to add the DDL to generate the views:

geography_columns

and

geometry_columns

But Im not sure if it's all here... I need add more?

Hey! I'm trying to create an app with 1.

collections

2.

user_collections

(join table between the two and gives a user access to a

collection

I've setup RLS such that +

select

is only for

collections

where there's a

user_collections.collections_id = collections.id AND user_collections.user_id = user.id

which is working +

insert

is only for

authenticated

users since I want to let anyone do it The problem is that because

insert

seems to use

select

I can't actually insert even though my logic seems to be right The workaround is that I can get

insert

to work if I use

{returning: "minimal}

so that it doesn't return anything (https://supabase.com/docs/reference/javascript/insert#notes) but then I don't have the

id

to create the join table and give permissions for the

select

Any thoughts on how to do this correctly? I imagine join table permissions is a very common design that there's probably an elegant solution I'm missing

No easy work around. Either have to generate own unique ID at client so you know it, (something like uid and time) or without thinking about it too much use an rpc function for the insert and then return your value, or do the rest of your operation on the other table at the same time in the function. Warning, late for me and just throwing out ideas to think about.

@User please create a thread as its easier to keep track of the conversation there.

RLS with Join Table Permissions

Is it possible to join a table with itself with supabase-js? I have this right now, but I'm unsure what I need to do to make it work:

javascript
  const { data, error } = await db
    .from('source_products')
    .select('*, source_products!inner(*)')
    .eq('id', id)
    .eq('source_products.main_sku', 'how do I reference this')
    .limit(1)
    .maybeSingle()

Basically I want to join in other products that have the same value for

main_sku

. Do I need to alias both 'tables' (it's the same table of course) This works:

sql
select t1.*, t2.* from source_products t1 left join source_products t2 on t1.main_sku = t2.main_sku where t1.id = '4bfbf03d-9707-4b03-ac44-04fb7296a91a'

How do I do the same with supabase-js?

self join

Howdy! I'm finding RLS Policy's to be the most challenging part of building on Supabase. Any tips for debugging and/or speeding up the feedback loop?