Hi,
I'm currently working on improving Npgsql's NpgsqlCommandBuilder.DeriveParameters method which is intended to automatically populate parameter information for a NpgsqlCommand.


As Shay Rojansky suggested to use a Parse/Describe -> ParameterDescription/RowDescription over the backend protocol instead of querying pg_proc (https://github.com/npgsql/npgsql/pull/912#issuecomment-167557036) in order to derive the properties of Parameters (data type, name, direction) I'm currently investigating this approach. The benefit of this would be that we could avoid duplicating quite a bit of backend functionality in order to find the correct overload of a specified function. Also it seems to be the best way to derive parameters from prepared SQL-statements that are not function calls.

While having a closer look at the details of the ParameterDescription/RowDescription that the backend returns after a Parse/Describe message I come to the conclusion that there is no valid way to always find out whether a parameter is IN or INOUT from these Messages.

Example:

Given the following function
CREATE OR REPLACE FUNCTION my_func(IN param1 integer, OUT param2 integer, INOUT param3 integer) RETURNS record AS
'BEGIN
param3 = param1 + param2 + param3;
END;' LANGUAGE 'plpgsql';

After sending a Parse message for 'SELECT* FROM my_func($1,$2)' followed by aDescribe message I'll get back a ParameterDescription message containing the OIDs of the two inwards bound parameters (and a void OID for the OUT parameter) followed by a RowDescription message containing the names and OIDs of the two OUT parameters.

Without additional knowledge of the exact function definition (parsing it or hardcoding information about it) I can only figure out that there are three parameters in total with two of them being inwards bound and two of them being outwards bound. I can also tell that the second parameter is a real OUT Parameter (from void OID in the ParameterDescription message). But what I can't tell by any means is whether the first parameter is the INOUT one or the last Parameter is the INOUT one i.e. wheter it's (IN,OUT,INOUT) or (INOUT,OUT,IN)

Digging around in the history of PostgreSQLs OUT and INOUT parameter support (http://www.postgresql.org/message-id/flat/421eca30.8040...@samurai.com#421eca30.8040...@samurai.com) and poking around in the respective commits (git log --after="2005-01-19" --before="2005-11-08" --author="Tom Lane" --grep="OUT") helped me to understand why things are like they are (i. e. why OUT Parameters are implemented more like rows than like parameters and why the ParameterDescription message gives so little information about them) but still I'd whish that the ParameterDescription message would contain the whole Information about all the parameters (name, type, direction).

Anyways, as I don't expect you to change the Frontend/Backend Protocol due to my whishes I just want to confirm that things really are the way I understand them and that I'm not overlooking something obvious.

If I'm right ParameterDescription path is probably a blind end for parameter derivation in Npgsql and I'll probably have to stick with the "query pg_proc"-way.

Regards,
Brar



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to