Hello everyone, In codeigniter 3.1.0, using the pg_query tool, I call the PostgreSQL 9.3 function

updateProduct( 0, 'Title text...', 'A', 'abr001', 1, 74.29, 53.81, 0, ' short description text...', ' description text...', 1 )

And I get the error:

 Message: pg_query(): Query failed: ERROR: function updateproduct(integer, unknown, unknown, unknown, integer, numeric, numeric, integer, unknown, unknown, integer) does not exist LINE 1: select * from updateProduct( 0, 'Title text...', 'A', 'abr0... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

And it is not clear why a bunch of unknowns ... The function is defined as:

 drop function if exists updateProduct(); create or replace function updateProduct( p_id integer, p_title varchar(100), p_status Type_ProductStatus, p_sku varchar(100), p_user_id smallint, p_regular_price Type_Money, p_sale_price Type_Money, p_in_stock boolean, p_short_description varchar(255), p_description TEXT, p_has_attributes boolean ) returns int as $function$ declare r product%rowtype; begin ... end $function$ language 'plpgsql';

Also identified are 2 types I specified in the function definition:

 CREATE TYPE Type_ProductStatus AS ENUM ( 'A', 'I', 'D', '-' ); CREATE DOMAIN Type_Money AS numeric(10, 2) CHECK (VALUE >= 0.);

I know that to bring tapov use

  cast(? As varchar)

What is the right way then ?

Thank!

    2 answers 2

    , p_has_attributes boolean )
    'description text ...', 1 )

    And I see one more identical argument, but I don’t want to consider its position.

    1 is not a boolean, it is an int. Postgresql forces you to monitor data types and does not allow you to shoot yourself a leg in such a simple way. Boolean is true and false, and pass them.

     updateProduct(..., true);

    By the way, pay attention to the name of the function in the text of the error. lowercase, entity names are always lowercase. The parser led to lower case itself, but it is better to stick to the lower case naming scheme and through the underscore for objects in the DBMS, update_product .

    Well, since I still write the answer - an obvious misunderstanding here:

     drop function if exists updateProduct(); create or replace function updateProduct

    create or replace normally update the body of the function if it already exists. Changing the output parameters, for example, cannot update, and such a drop function will not help here either.

     create or replace function updateProduct() ... create or replace function updateProduct(t_id int) ... create or replace function updateProduct(t_id int, name text) ...

    We have just created 3 different functions , but not updated the same one. In postgresql , there can be many functions with the same name, but a different set of arguments. Therefore, the drop function should be called with an indication of the types of arguments of the function to be deleted.

      Thanks for the clarification - it helped! And 2 more questions: 1) Is it possible to write a drop function so that ALL functions are deleted, regardless of the parameters? 2) Is it possible to transfer an array and if so how? In the forehead it is to pass a string with delimiters like "23,76,65" and on the server parsit and execute. Is there a better solution?