Idea: register function in postgres-compatible way with plv8

[mmkal]

It occurred to me that as well as the API-way of registering a function:

db.public.registerFunction({
            name: 'say_hello',
            args: [DataType.text],
            returns: DataType.text,
            implementation: x => 'hello ' + x,
})

Might it also be possible to enable "real" functions defined via SQL, as long as they use language plv8?

e.g.

create function say_hello(x text) returns text as
$func_delimiter$

return 'hello ' + x

$func_delimiter$
language plv8;

If pgsql-ast-parser is able to parse the create function query, it could be automatically mapped to the arguments required for registerFunction, where implementation is the eval(...) result of the body. That way, at least for plv8 functions, it'd be totally valid both in pg-mem and real postgres.

I'm interested in this as I have a library which implements row tracking through an in-memory JS git implementation: https://npmjs.com/package/plv8-git - I'm super curious if pg-mem could be used somehow to come full circle and even run the database in memory too.

Edit: just seen you have discussions enabled. I would have created this over there if I'd noticed, since it's not an issue. Feel free to move if you prefer it there too!

[oguimbal]

There used to be a "convert issue to discussion", but it has disapeared 🙄

This is a tough nut to crack.

While allowing simple plv8 functions creations (like the hello world you provided) might be simple, having something actually useful (like supporting plv8-git) is pretty hard.

Here are my reflections on your specific use case:

1. You would also need triggers (which are not implemented)
2. I dont want to have an eval() in pg-mem (it would probably be flagged as dangerous). This however could be circumvented by a language-declaration mechanism like this:

db.public.registerLanguage('plv8', code => eval(code));

... which would actually be more elegant, since it allows declaring arbitrary languages.

3. The above solution would force you to implement plv8 mocks yourself to support calls like plv8.find_function('myfunc'), which brings yet another level of complexity to the end user.

4. It would be a bit weird to only support functions declared in js, not in actual sql (but I'm not that uncomfortable with that, though)

5. Given that the thing you're trying to achieve is running JS, I feel that the only added value it would bring compared to declareFunction is just to test the syntax of something very simple like "create function".

So in the end, it seems reacheable, but a bit complicated compared to what it does.
Not sure i'll have enough time to work on that soon.

But that's definitely an appealing idea :)

[mmkal]

Fair enough - for a very limited use case this wouldn't be too hard to implement as a user of pg-mem anyway, so maybe I'll take a look at doing it specifically for plv8-git at some point (if triggers/function syntax are supported one day).

I love the idea of registerLanguage, it's nicely analogous to how real postgres languages work, and agreed that it's always a good thing to avoid eval(...) calls in libraries that users might not know about.

[dschinkel]

There's a new Discussions tab at top, new feature on GitHub