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
