Menu ▾ ▴

#316 PostgreSQL: Add support for PG procedures

7.2.4
closed
EgonHugeist
None
2019-11-16
2018-11-22
marsupilami79
No

This has been discussed on the forums. See http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=83796.

PG 11 introduces a new feature: callable procedures. Procedures can use transaction control whereas functions in PG cannot do that. Both are managed in the same system tables so Zeos currently tries to use them in the same way as functions get used. This results in a syntax error because functions need to be called using an select staement whereas procedures need to be called using call.
As a part of this PGs metadata has changed: pg_proc.proisagg and pg_proc.proiswindow were replaced by pg_proc.prokind. See "E.2.2. Migration to Version 11" in the PG manual.

So essentially we have to do the following:

  • Modify UncachedGetProcedures and maybe UncachedGetProcedureColumns to use the new metadata field, if PG11+ is detected. GetServerVersion might help to determine the current server version.
  • Modify PGs implementation of IZCallableStatement to use the correct calling convention. It either needs to be determined from GetProcedures or it needs to make its own call into the database to determine this. In the latter case again GetServerVersion might be helpful.
  • Worry about overloaded functions later on - I assume Zeos PostgreSQL driver doesn't worry about this at all currently.

Related

Wiki: Things to know about the Zeos 7.2 release

Discussion

  • marsupilami79

    marsupilami79 - 2018-11-22
    • Description has changed:

    Diff:

    --- old
+++ new
@@ -1,11 +1,11 @@
 This has been discussed on the forums. See http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=83796.

-PG 11 introduces a new feature: callable procedures. Procedures can use transaction contropl whereas functions in PG cannot do that. Both are managed in the same system tables so Zeos currently Zeos tries to use tehm un the same way as functions get used. This results in a syntax error because functions need to be used by a select staement whereas procedures need to be called using call.
+PG 11 introduces a new feature: callable procedures. Procedures can use transaction control whereas functions in PG cannot do that. Both are managed in the same system tables so Zeos currently tries to use them in the same way as functions get used. This results in a syntax error because functions need to be called using an select staement whereas procedures need to be called using call.
 As a part of this PGs metadata has changed: pg_proc.proisagg and pg_proc.proiswindow were replaced by pg_proc.prokind. See "E.2.2. Migration to Version 11" in the PG manual.

 So essentially we have to do the following:

 * Modify UncachedGetProcedures and maybe UncachedGetProcedureColumns to use the new metadata field, if PG11+ is detected.  GetServerVersion might help to determine the current server version.
 * Modify PGs implementation of IZCallableStatement to use the correct calling convention. It either needs to be determined from GetProcedures or it needs to make its own call into the database to determine this. In the latter case again GetServerVersion might be helpful.
-* Worry about overloaded functions later on - I **assume** Zeos PostgreSQL driver doesn't worry about this currently at all.
+* Worry about overloaded functions later on - I **assume** Zeos PostgreSQL driver doesn't worry about this at all currently.
     

  • marsupilami79

    marsupilami79 - 2019-05-12

    Modify UncachedGetProcedures and maybe UncachedGetProcedureColumns to use the new metadata field, if PG11+ is detected

    Most probably GetProcedures will already list procedures because it doesn't do any filtering. Currently the PostgreSQL driver will hard code ProcedureReturnsResult in the PROCEDURE_TYPE field.

    For Zeos 7.2 I suggest to return procedureNoResult in the PROCEDURE_TYPE field so we can distinguish procedures from functions.
    For Zeos 7.3 I suggest to use the approach described in [#349] - "Zeos 7.3 + stored functions in databases".

    Opinions?

     

    Related

    Feature Requests / Suggestions: #1

  • EgonHugeist

    EgonHugeist - 2019-10-30
     

  • EgonHugeist

    EgonHugeist - 2019-10-30

    Any example to play with?

     

  • EgonHugeist

    EgonHugeist - 2019-11-16
    • status: open --> closed
    • assigned_to: EgonHugeist
     

  • EgonHugeist

    EgonHugeist - 2019-11-16

    i've replaced the hard coded PROCEDURE_TYPE by testing the prokind field for SP's(tagged by the 'p' char).
    Full 'CALL' syntax supportd added including test.
    Patch done R6140 /tesing-7.3
    Ticket 349 is another discussion...

     

Log in to post a comment.