Pg-promise: Native stored procedures in PostgreSQL v11

[realcarbonneau]

The documentation indicates that proc(procName, valuesopt, will call SELECT * FROM procName(values).
http://vitaly-t.github.io/pg-promise/Database.html#proc

However, PostgreSQL does not permit this. CREATE PROCEDURE exists only since PostgreSQL version 11, and it requires the use of CALL proc(), not SELECT * FROM proc(). Using a Select give the error "ERROR: proc() is a procedure. ... HINT: To call a procedure, use CALL."

https://www.postgresql.org/docs/11/sql-createprocedure.html
https://www.postgresql.org/docs/12/sql-createprocedure.html

https://www.postgresql.org/docs/11/sql-call.html
https://www.postgresql.org/docs/12/sql-call.html

Obviously, there are many workaround, so this is by no means an important issue. I just thought it might be useful to document document it for you in case no one had reported it yet.

[vitaly-t]

A stored procedure within PostgreSQL has always been a reference to a function that does not return anything. And generic syntax SELECT * FROM func used by method [proc] is perfectly correct for that.

Just because PostgreSQL decided to add a separate definition for procedures in the most recent version of the server does not invalidate the approach used in this library. It may only be a little confusing, because of the naming and what it does.

To make method [proc] use the newest call syntax would be a breaking change in the library, one that requires a major version upgrade. The worst part - it would not work in PostgreSQL version prior to v11, while this library, as of now, supports all existing PostgreSQL versions.

[vitaly-t]

This will likely require detecting the server version upon connection, to figure out if the new syntax is supported, and so I have done a bit of research into that.

[realcarbonneau]

You would know what the best solution is considering the big picture of pg-promise and PostgreSQL. A simple option would be to have a db.call() in pg-promise and that it simply passes on PostgreSQL's error when used on versions before v11. I am just providing ideas, you know best and this is not a critical issue as there are many existing workarounds.

Thanks again! :)

[vitaly-t]

I probably wouldn't use call as a method name, because it is a reserved JavaScript routine for passing context into functions. But let's call it callProc, for example...

You can [extend] the protocol with such method:

const pgp = require('pg-promise')({
    extend(obj) {
        obj.callProc = (name, values) => {
            const query = pgp.as.format('CALL $1:alias($2:list)', [name, values]);
            return obj.none(() => query);
        }
    }
});

[realcarbonneau]

obj.callProc seems perfect to me and avoids you having to check the version, letting it fail when the backend PostgreSQL errors out.

[vitaly-t]

Since there was a suggestion that server version detection can be useful, here's #667, with the recent update that shows how to get the server version nicely, without executing any query.

[vitaly-t]

@realcarbonneau Now with #667 complete, I feel there may be a good sense in deleting method [proc] from the protocol altogether, as its implementation should depend on the server version, and this library supports versions of PosgtreSQL all the way back to v8.

I'm not certain though, I am meditating on this. Any thoughts?

You see, the alternative would require a bit of change, bringing in server version checks, and then formatting the query based on that. And I'm not sure if it would be a good idea, though it's possible.

[realcarbonneau]

@vitaly-t Maybe I am missing something, but there doesn't seem to be a simple answer:
1) If you drop proc you break legacy
2) If you convert proc to 'CALL proc()you break legacy, but shorten and simplify the call syntax. 3) You could keep [proc](https://vitaly-t.github.io/pg-promise/Database.html#proc) and direct it toSELECT * FROM func()orCALL proc()but that will add an extra call to the db to identify the target func/proc type based on it's signature and db version, and you would have to cache that to avoid a large hit to performance. So this is quite complex. 4) You could add a warning to [proc](https://vitaly-t.github.io/pg-promise/Database.html#proc) that it will be deprecated in x versions/years and direct users to [func](http://vitaly-t.github.io/pg-promise/Database.html#func) and callProc() and a flag to turn off the warning. 5) You could add a temporary switch parameter with a warning that permits selection between proc() calling a function and calling a procedure. And make it clear that within x version/years, proc will always go toCALL proc(). This would avoid breaking legacy for a while, your current version would have the switch set to keep proc() as is by default. Your next version would set proc() toCALL proc()by default. I personally prefer this as it is shorter and seems to better follow your existing nomenclature. 6) Or just impose proc() toCALL proc()` as of the next version.

Just suggestions. The easiest workaround for the moment is simply to change the PROCEDUREs to FUNCTIONs in PostgreSQL or make a FUNCTION wrappers when changing the PROCEDURE is not possible.

[vitaly-t]

I believe that doing nothing is also an option :) I mean, it works, don't it? :)

[realcarbonneau]

Ultimately, your current proc() is simply a redundant synonym for your func(). Additionally, since pg v11 CALL proc(), your proc() also becomes confusing. It should eventually be changed to map to CALL proc() imho. When and how you do this, is up to you, but my guess is that over time, this will become the best option.

If you break existing dependent projects during a major version upgrade of pg-promise, it won't be difficult for the developer to search & replace proc to func.

[vitaly-t]

I had a look into adding version check, and it seems like a nightmare, almost impossible. So I'm gonna play it safe, make a breaking change going to v10, and either delete it altogether or change it to use CALL.

[vitaly-t]

Created #670 to track progress on this.

[vitaly-t]

This has been concluded within #670, and the change was released in v10.0.0

[vitaly-t]

The feature has been updated, to support output parameters - see #692