[RESOLVED] What category is (On message creation- delete oldest message if there are 500 messages)

For example I have a table called 'messages' and every time a message is added to the table;
And I want it to check if there are 500 messages; and if so- delete the oldest message from the table.

I'm not sure what this would fall under: edge functions, plain js, idk. I'm new to this stuff.

garyaustin•12/2/22, 2:58 PM

You could use an insert trigger function to count the rows of the table and delete. I have no idea though how efficient this is. I guess if you have some sort of count in the messages, if that is greater that 500 then you always do the delete on insert would work also, but you can’t delete the messages any other way with this 2nd approach.

Ggaryaustin You could use an insert trigger function to count the rows of the table and dele...

GudBoiNeroOP•12/2/22, 5:12 PM

Is the first approach Database Functions? If so I’m having a tough time understanding it. Any code I find using glgsql is broken

GudBoiNeroOP•12/2/22, 5:13 PM

I make a trigger that calls on insert and I can’t figure out how to make a method since I can’t even find the resources that explains the language.

garyaustin•12/2/22, 5:13 PM

Both approaches are using a Postgres database trigger function. Just different methods to determine if you have 500 rows already.

GudBoiNeroOP•12/2/22, 5:14 PM

Off the top of your head do you know anywhere you can redirect me?

garyaustin•12/2/22, 5:14 PM

There are 1000's of articles on the web on using Postgres functions and I'm sure there are some on getting the count of a table.

GudBoiNeroOP•12/2/22, 5:15 PM

Alright I’ll see what I can find

GudBoiNeroOP•12/2/22, 5:15 PM

I found a ton of them but every time I tried to use them it told me the syntax was invalid

GudBoiNeroOP•12/2/22, 5:15 PM

Sorry should’ve specified that before

GudBoiNeroOP•12/2/22, 5:16 PM

I know that often sql editors (or whatever they’re called, postgres, MySQL etc) normally have differences in their syntax. I think it’s just screwing me up a ton

garyaustin•12/2/22, 5:17 PM

This is a simple guide... https://www.postgresqltutorial.com/postgresql-plpgsql/postgresql-create-function/

PostgreSQL Tutorialadmin

PostgreSQL CREATE FUNCTION By Practical Examples

In this tutorial, we will show you step by step how to develop the fist user-defined function using PostgreSQL CREATE FUNCTION statement.

GudBoiNeroOP•12/2/22, 5:21 PM

Oh I see

GudBoiNeroOP•12/2/22, 5:21 PM

So I’m supposed to do that in the Query Editor

GudBoiNeroOP•12/2/22, 5:21 PM

Thanks :)

garyaustin•12/2/22, 5:36 PM

You can also use the database function UI. If you select your language as plpgsql then you put everything between and including begin.... end; it the window.

garyaustin•12/2/22, 5:37 PM

https://supabase.com/docs/guides/auth/managing-user-data#using-triggers shows an example of a trigger function

Supabase Documentation

Managing User Data

Securing your user data with Row Level Security.

[RESOLVED] What category is (On message creation- delete oldest message if there are 500 messages)

Similar Threads

Similar Threads

Similar Threads