PostgreSQL 11 introduced support for SQL procedures. dotConnect for PostgreSQL has some specificity in executing PostgreSQL stored procedures, in comparison to stored functions.

To run a stored procedure, a PgSqlCommand object must know that this is a stored procedure, and not a stored function. It can be achieved in two ways.

First, you can set the ParameterCheck property of the command to true. In this case, will automatically query the necessary information, together with the list of parameters from PostgreSQL when assigning the command text. However, this leads to an additional roundtrip to the server, which can be undesirable.

Alternatively, you can explicitly specify that you are calling a stored procedure by adding the suffix ":proc" to the stored procedure name. For example:

      PgSqlCommand cmd = new PgSqlCommand(query, conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText= "public.insert_data:proc";

    Dim cmd As PgSqlCommand = New PgSqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "public.insert_data:proc"

If you specify this suffix, PgSqlCommand will run the specified routine as a procedure. You don't need to add any specific suffixes for functions.

As well as for functions, stored procedures can be overloaded in PostgreSQL - there can be multiple stored procedures with the same name but different set of parameters. In this case, to determine a correct overload, the 1-based number of the corresponding overload is used as a suffix, as well as for functions:

cmd.CommandText= "public.insert_data2:proc:3";

cmd.CommandText= "public.insert_data2:proc:3"

See Also

 PgSqlCommand Class  | Using Parameters