Has anyone used

PGP_SYM_ENCRYPT

or have any examples?

Hmm but the problem with this is there's no row yet right? Since it's before insert.

You might be right about that, but it could be worth a try. It's been a long day and my brain isn't working properly right now, but it's possible that it creates the row with

admin_id

set to

auth.uid()

and then inserts the other values in the same row - sort of like modifying it in-place before committing the transaction to the DB. Another option is to put the other values from

new

into the query: e.g.

INSERT INTO public.my_table(admin_id, some_string, some_number) VALUES (auth.uid(), new.some_string, new.some_number)

. Another option which might work would be to set the column default to

auth.uid()

. That way, whenever a new row is inserted, it'll automatically set the value of the

admin_id

column to

auth.uid()

.

Insert trigger to change value for new before insert

Sometimes a RLS Policy subquery can have a name conflict and silently use the wrong context! FUN. Like, I have a table that has column

t1.blah_blah

and I wanted to use that column in a subquery who also had a column of the same name (

... where foo.blah_blah = blah_blah

). Instead of warning me, it just used the unqualified name from

foo

.. which is always true, and really tripped me up.

Had to fix it by using a

with

clause. Here's my redacted SQL:

USING (
  -- .. snip ..
  and
  user_id in (
    with foo as (select blah_blah) 
    select 
      owner_id 
    from 
      wave_blips wb, 
      foo 
    where 
      wb.blah_blah = foo.blah_blah 
    and 
      wb.owner_id = user_id
)

I'm just annoyed. that is all.

took me like 45 minutes staring at it going "WHY?!"

because I'm too lazy to set up a local env, I had no way of debugging via

raise notice

or whatever, and there's no way to "run" a policy other than using it

Debugging SQL

Update: turns out I was wrong and @User pointed out I could have just qualified it using the table name to which the policy applies.

Maybe I should have put this question here: https://canary.discord.com/channels/839993398554656828/843999948717555735/878463314840158280

How do I SELECT a record by using a property inside some JSONB as the id? Structure:

table "mysuperspecialtable"
  id: TEXT = 'activeUsers',
  value: JSONB =
    {
      "activeUserId": "abcdef"
    }

Something like:

SELECT
          username,
          avatarurl
    FROM mysuperspecialtable
    LEFT JOIN users ON users.id = (mysuperspecialtable.value ->> 'activeUserId')::TEXT
    WHERE
        mysuperspecialtable.id = 'activeUsers'

How do I grab the user's record using that property? I have tried everything I can think of (Some context is I am converting Firestore to PostgreSQL and I stored some references to docs in an array)

Why does PostgREST give me a 404 error when I try to update a record that has a RLS policy that prevents updating? When I satisfy the RLS policy it returns 200

I have another database in my postgres instance and I can't delete it because supabase_admin is always accessing it

Hello all, anybody here who is a dart and postgres experts please help out. I am trying to make this query work. Here the contacts is a dart string variable with a lot of phone numbers in it with this format

'%91960000000’,’%7780000000',’%6720000000',’%4160000000','%7780000000'

. With this query I am not getting any errors, but I am supposed to get two records returned, but I get a blank array returned.

final results = await postgresConnection.query(
          "SELECT display_name,phone_number FROM public.users WHERE phone_number LIKE (@contacts)",
          substitutionValues: {"contacts": contacts});

@User Please do not ask in every channel. Plus, can you put your code in a code tag please? Using \`\`\` around your code

I was told to put it over here

I can remove from the other chat

Sure will do

Hi all, how can we get more details about the sql execution? I have 2 triggers that calls their own function on my insert one before the insert to update the column value and one to update another table, my problem is only the second one is happening and I can't seem to find any details why the other is not happening

I am out of forums to post the above question. It's been a week already. Anybody who has expertise in postgrest, supabase, dart please help out.

Debugging Triggers

does it work if you replace

LIKE

with

IN

?

i dont use dart but am assuming the '@' has a special meaning for substitution

oh sorry just saw you mentioned it's a string variable. what about

WHERE phone_number LIKE '%(@contacts)%'

?

The values inside the array has the percentage sign on it.

Like you see in the example above that's how the values of the contacts variable is rendered.

so it's an array? or is it just a long string?

It's a long string