You might need to add explicit type casts

Question

I am trying to create function which returns last inserted id, but its showing error while function call: Will you please help me to identify my error.

ERROR: function public.insert_voucher(integer, unknown, unknown, integer, integer, unknown) does not exist
LINE 1: select public.insert_voucher(1, 'P', '20180909', 1, 1, 'txt');

Function:

CREATE OR REPLACE FUNCTION public.insert_voucher(
     in_orgid smallint
    ,in_transtype character
    ,in_date character
    ,in_partnerid smallint
    ,in_quantity smallint
    ,in_remarks character varying)
  RETURNS integer AS
$BODY$
BEGIN
insert into 
public.transaction_header(
   org_id
  ,trans_type
  ,fiscal_year
  ,date
  ,partner_id
  ,quantity
  ,remarks
  ,create_by
  ,create_ts)
values (
   in_orgid
  ,in_transtype
  ,1819
  ,in_date
  ,in_partnerid
  ,in_quantity
  ,in_remarks
  ,1
  ,now())
returning trans_header_id;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Table Schema:

trans_header_id integer NOT NULL DEFAULT nextval('transaction_header_trans_header_id_seq'::regclass)
,org_id smallint NOT NULL
,trans_type character(1) NOT NULL DEFAULT 'P'::bpchar
,fiscal_year smallint NOT NULL DEFAULT '1819'::smallint
,date date NOT NULL
,partner_id smallint NOT NULL
,quantity smallint NOT NULL
,remarks character varying(100)
,create_by smallint NOT NULL
,create_ts timestamp without time zone NOT NULL DEFAULT now()
,update_by smallint
,update_ts timestamp without time zone
,CONSTRAINT transaction_header_pk PRIMARY KEY (trans_header_id)
,CONSTRAINT create_by FOREIGN KEY (create_by)
REFERENCES public.app_user (user_id) MATCH FULL
          ON UPDATE NO ACTION ON DELETE NO ACTION
,CONSTRAINT org_id FOREIGN KEY (org_id)
          REFERENCES public.organization (org_id) MATCH FULL
          ON UPDATE NO ACTION ON DELETE NO ACTION
,CONSTRAINT partner_id FOREIGN KEY (partner_id)
          REFERENCES public.partners (partner_id) MATCH FULL
          ON UPDATE NO ACTION ON DELETE NO ACTION
,CONSTRAINT update_by FOREIGN KEY (update_by)
          REFERENCES public.app_user (user_id) MATCH FULL
          ON UPDATE NO ACTION ON DELETE NO ACTION
,CONSTRAINT org_fy_transtype_transno UNIQUE (org_id, trans_type, fiscal_year)

With reference @muistooshort and @stickybit, I am updating previous function. Hope it will give more clarity and returns desired result.

CREATE OR REPLACE FUNCTION public.insert_voucher(
    IN in_orgid smallint
    ,IN in_transtype character
    ,IN in_date date
    ,IN in_partnerid smallint
    ,IN in_quantity smallint
    ,IN in_remarks character varying
    ,OUT out_id smallint)
  RETURNS smallint AS
$BODY$
    BEGIN
    insert into 
    public.transaction_header(
       org_id
      ,trans_type
      ,fiscal_year
      ,date
      ,partner_id
      ,quantity
      ,remarks
      ,create_by
      ,create_ts)
    values (
       in_orgid
      ,in_transtype
      ,1819
      ,in_date
      ,in_partnerid
      ,in_quantity
      ,in_remarks
      ,1
      ,now())
    RETURNING trans_header_id
    INTO out_id;
    END
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

user_9661424 · Answer

The smallints are the problem. A cast from integer to smallint can mean losing some information. The engine won't do implicit casts, where information can get lost. Therefore it considers public.insert_voucher(integer, unknown, unknown, integer, integer, unknown) as not an option.

If you explicitly cast the numbers to smallint, the call should work.

SELECT public.insert_voucher(1::smallint, 'P', '20180909', 1::smallint, 1::smallint, 'txt');

There are some other things like why you pass a date as a string and '1819'::smallint (why the string first?). And some other problem may arise once the call works. But that's beyond the current question.

You might need to add explicit type casts

1 Answers