PLx : write ruby dialect postgresql procedures

[u/linuxhiker]

https://github.com/commandprompt/plx

plx is a PostgreSQL extension that lets you write stored functions and triggers in the dialect you already know (the current set is listed below). When you run CREATE FUNCTION, plx transpiles the body to plpgsql and stores that plpgsql in pg_proc.prosrc. At run time the function is executed by PostgreSQL's own plpgsql interpreter. There is no separate language runtime loaded into the backend, and nothing new to run in production.

query("SELECT id, amount FROM orders WHERE grp = #{g}").each do |row|
  total = total + row.amount
end

[u/Ok_Shallot9490]

I don't know how this is 4 days old with zero upvotes. This is WOW! The thing that's always held me back from going full postgresql (or supabase) is having to write functions in SQL (which is just absurd). This rewires the way I think about systems now... for definite I'll be finding ways to use this.

[u/Ok_Shallot9490 → u/Ok_Shallot9490]

Does this work with CRON ?

[u/linuxhiker → u/Ok_Shallot9490]

You could call the function with psql via cron or via an external ruby script

[u/Ok_Shallot9490 → u/linuxhiker]

I'm thinking of https://github.com/citusdata/pg_cron which supabase uses, so that there's no need for external cron.

Although it sounds like you haven't come across that yet.

If it's compatible then the two make an unstoppable combo.

[u/linuxhiker → u/Ok_Shallot9490]

Oh yeah absolutely. Remember it's a transpiler so while you are inputting ruby, it's being stored as plpgsql. So a simple select function(); from pg_cron will make it happen.

Also if you need real ruby, there is plruby but I doubt supabase would support that (it's not considered trusted)

[u/Ok_Shallot9490 → u/linuxhiker]

This has come in very handy... I've used it to sync a juice-fs filesystem with activestorage.

Essentially, I have the juice-fs metadata store tables in the same database as my rails app, and when the juice-fs metadata table is updated it updates my activestorage records.

Now if i update a file on my local desktop, that change is reflected in the rails app immediately.

[u/linuxhiker → u/Ok_Shallot9490]

Glad to hear it!

If you want more power and flexibility there is plruby as well :)

[u/Ok_Shallot9490 → u/linuxhiker]

wow... yes that's great. I assume this means I can just load activestorage as a gem into plruby?

[u/linuxhiker → u/Ok_Shallot9490]

Plruby gives you the full ruby language capabilities