Infer INOUT Parameters from Frontend/Backend Protocol - Mailing list pgsql-hackers

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.8040007@samurai.com#421ECA30.8040007@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