Pg-promise: Postgres v11+ Native Stored Procedures

Created on 9 Nov 2019 · 24Comments · Source: vitaly-t/pg-promise

This features is a follow-up on #665

PostgreSQL v11 introduced support for stored procedures.

Method [proc] needs to be re-done to account for that. Currently, it just forwards into [func], which executes SELECT * FROM procName(values), and then resolves with one or no value.

This creates a confusion, moving forward, and needs to be changed, so that [proc] would execute the new CALL procName(values) instead, and resolve with null.

This will be a breaking change, and method [proc] will throw a server-side error, if executed against PostgreSQL server prior to v11. It will be the only version-sensitive method in the library.

Help Wanted improvement

Source

vitaly-t

👍1

All 24 comments

I have done initial implementation in branch proc, but Travis CI gave me trouble upgrading PostgreSQL to v11 within .travis.yml. I have tried a few things, including this one, but no luck.

And so I cannot include new tests for stored procedures into automated tests.

Any help with this would be very welcome! Until then, I'm putting it on hold.

@realcarbonneau F.Y.I.

vitaly-t on 9 Nov 2019

While working on this, found #671

vitaly-t on 9 Nov 2019

@vitaly-t I have been testing your new proc, but it's not matching up the proc signature, even sending parameters as strings. I will trace and get back to you with more details.

realcarbonneau on 9 Nov 2019

@realcarbonneau I have tested it here, and it works fine. Make sure you use the latest from the proc branch, which now includes the fix for #671

vitaly-t on 9 Nov 2019

Works for me:

CREATE TABLE test(
  id       SERIAL PRIMARY KEY NOT NULL,
  ts       TIMESTAMP WITH TIME ZONE,
  test_int INTEGER
);

CREATE OR REPLACE PROCEDURE test_proc(in_int INTEGER)
LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    -- Nothing
  BEGIN
    -- Insert into Test
    INSERT INTO test (ts, test_int) VALUES (CLOCK_TIMESTAMP(), in_int);
  END
$BODY$;

CREATE OR REPLACE FUNCTION test_func(in_int INTEGER) RETURNS BOOLEAN
LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    -- Nothing
  BEGIN
    -- Insert into Test
    INSERT INTO test (ts, test_int) VALUES (CLOCK_TIMESTAMP(), in_int);
    RETURN TRUE;
  END
$BODY$;

const procResult = await db.proc('test_proc', 3)
const funcResult = await db.func('test_func', 4)

2019-11-09 14:29:09.065 EST [17916] LOG:  statement: call test_proc(3)
2019-11-09 14:29:09.078 EST [17916] LOG:  statement: select * from test_func(4)

realcarbonneau on 9 Nov 2019

Great! Also, as per the #671 fix, if you name it something like testProc, it will be automatically double-quoted, i.e. as CALL "testProc"().

vitaly-t on 9 Nov 2019

Doesn't seem to work for me.

CREATE OR REPLACE PROCEDURE testProc(in_int INTEGER)
LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    -- Nothing
  BEGIN
    -- Insert into Test
    INSERT INTO test (ts, test_int) VALUES (CLOCK_TIMESTAMP(), in_int);
  END
$BODY$;

const procResult = await db.proc('test_proc', 3)
const proc2Result = await db.proc('testProc', 4)
const funcResult = await db.func('test_func', 5)

2019-11-09 15:01:52.142 EST [14864] LOG:  statement: call test_proc(3)
2019-11-09 15:01:52.162 EST [14864] LOG:  statement: call "testProc"(4)
2019-11-09 15:01:52.163 EST [14864] ERROR:  procedure testProc(integer) does not exist at character 6
2019-11-09 15:01:52.163 EST [14864] HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
2019-11-09 15:01:52.163 EST [14864] STATEMENT:  call "testProc"(4)

realcarbonneau on 9 Nov 2019

@realcarbonneau It doesn't work for you because it thinks you declared procedure named "testProc", and not testProc. Do you know how camel case works in PostgreSQL? :smile:

If you want to create a procedure in camel case, then you need to user double quotes:

CREATE OR REPLACE PROCEDURE "testProc"

vitaly-t on 9 Nov 2019

Well it works on the db directly, so your call should work also.

But I know what you mean. If the coder wrote camelCase on the js side, you will identify this and always add quotes around it, instead of asking the coder to follow strictly the string literal "camelCase" on both sides.

If I pass the literal "char" to the db, that is different than char. I prefer that my strings are not changed. But it's your choice.

Either way, I always snake_case on the db side and camelCase in js and use the case-change if I need to translate. Therefore I am indifferent on this issue other than from a theoretical perspective that when you do invasive modifications of string literals, it should to be consistent and coherent across your framework.

realcarbonneau on 9 Nov 2019

You are going to curse! hahaha

FUNCTIONs are case sensitve in quotes, but not PROCEDUREs. See screenshots below. So the whole thing is useless for PROCEDUREs.

I would always pass the string literal to the db, no changing it, or else the whole this is not coherent. What are you going to do when a wise guy calls his function "testfunc" litterally? See the last screenshot. It becomes a spaghetti mess of logic, documentation an explanations.

realcarbonneau on 9 Nov 2019

Well it works on the db directly, so your call should work also.

Nope. It works directly because you are not using the actual camel case. You need to either use it in both places, or none at all.

FUNCTIONs are case sensitve in quotes, but not PROCEDUREs

That's not true. Something is wrong in your test. Names of functions and procedures work the same in relation to the camel case.

vitaly-t on 9 Nov 2019

👍1

@realcarbonneau

Published 10.0.0 Beta.0, from the proc branch.

I don't want to merge until Travis CI is sorted for PG v11, and I don't know how long that's gonna take.

vitaly-t on 9 Nov 2019

👍1

You are correct, there was a problem in my prior test sequences that left a lower case version of the procedure. I think that I understand that your perspective is that since JS is case-sensitive and that since pg is case-insensitive (technical everything is forced to lower-case), you will preserve the cases of technical names by quoting the strings that contain at least one upper-case when sending them to pg. There is no specific concept of camelCase in pg, as someone could choose PascalCase or whatever they want. I assume that you preserve cases into pg for all technical names across your library? Best practices for pg are to keep everything lower case, such as by using snake_case, but if your camelCase and PascalCase and other miXedCaSe friends are happy, that is what matters! :)

realcarbonneau on 10 Nov 2019

I think that I understand that your perspective is that since JS is case-sensitive and that since pg is case-insensitive

I'm afraid, you still do not understand it. JavaScript case-sensitivity got nothing to do with it, because we supply function/procedure name as a string always.

There is no specific concept of camelCase in pg

Actually, there is. Camel-case handling within PostgreSQL is very specific, and I suggest you read about it. Otherwise you are making incorrect assumptions.

vitaly-t on 10 Nov 2019

Released in v10.0.0

vitaly-t on 10 Nov 2019

@vitaly-t Official PostgreSQL Documentation
0 results for camelCase
0 results for camel case

I have never found any reference to camel case in the official documentation. Of course, there is some discussion of camelCase in the mailling lists and elsewhere on the internet, however, it is always in the greater context of the case-insensitivity of SQL identifiers (SQL:2016 or ISO/IEC 9075:2016) and not specific to camelCase. Technically, case-insensitivity is actually achieved by the SQL standard folding identifiers to uppercase and postgreSQL folding identifiers to lowercase.

SQL Syntax - Lexical Structure Section 4.1.1
"Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)"

case-sensitive identifiers are an exception dealt with using quotes and the function quote_ident(string text)

ISO/IEC 9075:1992, page 100

4) An <SQL language identifier> is equivalent to an <SQL language
            identifier> in which every letter that is a lower-case letter
            is replaced by the equivalent upper-case letter or letters. This
            treatment includes determination of equivalence, representation
            in the Information and Definition Schemas, representation in the
            diagnostics area, and similar uses."

realcarbonneau on 10 Nov 2019

Not sure about the original, but there are tons about it on StackOverflow ;)

P.S. I'm wrapping up #673 right now :)

@realcarbonneau

vitaly-t on 10 Nov 2019

Ok, tell me if you need help on anything else, testing or coding. :)

I am now running v12, mostly.

realcarbonneau on 10 Nov 2019

Thank you! :) I'm all done with #673 - check it out ;)

@realcarbonneau

vitaly-t on 10 Nov 2019

Re-opening, to attempt pg v11 integration for tests yet again.