For some time I've been trying to get into PostgreSQL looking forward to use it as a RDBMS for a PyQt5 system. So far my main difficulty arises when trying to run a stored procedure where I usually get the 42883 error code. I already went through the need to cast the string variables to varchar and the date data to date which helped me sort same errors, but I couldn't pass through 42883. When checking the documentation, the error describes so many possibilities, some of them not to clear though, I couldn't get any clue from it. I tried enclosing table names with quotes with similar results. This is my first stored procedure try - even though simple is just a try since I foresee the need of using transactions -

   CREATE OR REPLACE PROCEDURE public.es_load_image(IN _coordinates character varying, IN _photo 
   character varying, IN _report_date date, IN _reporterid smallint, IN _categoryid smallint, IN 
   _description character varying)

   LANGUAGE plpgsql
    
   AS $$
      BEGIN
         INSERT INTO image_points
         (geom, photo, report_date, reporterid, categoryid, description)
         VALUES(ST_GeomFromText('POINT('||_coordinates ||')', 4326), _photo, _report_date, 
        _reporterid,_categoryid, _description);
          
END; 
$$

After running the stored procedure (PgAdmin4) as follows:

CALL es_load_image('-59.74553210 -35.75147550'::varchar,
               'C:/gis/photos/photo_13052022_162549.jpg'::varchar,
              '2022-05-13'::date, 3, 0, 'Just testing'::varchar)

I get this notice:

"ERROR: procedure es_load_image(character varying, character varying, date, integer, integer, character varying) does not exist LINE 1: CALL es_load_image('-59.74553210 -35.75147550'::varchar, ^ HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 6" I supposed there is a simple solution to this issue but I wasn't able to figure it out. Note: PostgreSQL 14.1

CodePudding user response：

To follow up on my comment:

CREATE OR REPLACE PROCEDURE public.es_load_image(IN _coordinates character varying, IN _photo character varying, IN _report_date date, IN _reporterid smallint, IN _categoryid smallint, IN _description character varying)
 LANGUAGE plpgsql
AS $procedure$
      BEGIN
         RAISE NOTICE '%, %, %, %, %, %', _coordinates, _photo, _report_date, _reporterid, _categoryid, _description;

END;
$procedure$

CALL es_load_image('-59.74553210 -35.75147550',
               'C:/gis/photos/photo_13052022_162549.jpg',
              '2022-05-13', 3::smallint, 0::smallint, 'Just testing')
;

NOTICE:  -59.74553210 -35.75147550, C:/gis/photos/photo_13052022_162549.jpg, 2022-05-13, 3, 0, Just testing

The casting is done according to this:

select castsource::regtype,  casttarget::regtype, castcontext from pg_cast where castsource = 'integer'::regtype;
 castsource |    casttarget    | castcontext 
------------ ------------------ -------------
 integer    | bigint           | i
 integer    | smallint         | a
 integer    | real             | i
 integer    | double precision | i
 integer    | numeric          | i
 integer    | money            | a
 integer    | boolean          | e
 integer    | oid              | i
 integer    | regproc          | i
 integer    | regprocedure     | i
 integer    | regoper          | i
 integer    | regoperator      | i
 integer    | regclass         | i
 integer    | regcollation     | i
 integer    | regtype          | i
 integer    | regconfig        | i
 integer    | regdictionary    | i
 integer    | regrole          | i
 integer    | regnamespace     | i
 integer    | "char"           | e
 integer    | bit              | e

Where from here pg_cast:

castcontext char

Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.