Re: Fwd: parameter type is unknown error

[Günce Kaya <guncekaya14@xxxxxxxxx>]

Hi Ray,

I think the problem is not associated with you suggestion cause I changed both of two parameter type as varchar(by the way, I deleted address column and I only have two column that got me crazy!) still doesn't insert any data.

you can see changes as I emphasized as bold.

create or replace function cargo.insertinvoice (forderid integer, fcargoid integer, finvoiceowner integer, freceiverfname varchar, freceiverlname varchar, freceiverphone integer, fsendingdatetime timestamp without time zone, fdistance real, fweight numeric,  finvoiceamount money, fcargocreateddate date, fcargoupdateddate timestamp, fcargocancelled timestamp without time zone) returns numeric as $$

declare v_id bigint;

begin

insert into cargo.invoice (orderid, cargoid, invoiceowner, receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values(forderid,fcargoid,finvoiceowner,freceiverfname,freceiverlname,freceiverphone, fsendingdatetime, fdistance,fweight,faddedtax,finvoiceamount,fcargocreateddate,fcargoupdateddate,fcargocancelled)

returning id;

end;

$$ language plpgsql;

I used bellow code to execute function;

select * from cargo.insertinvoice(1013, 10, 44,cast('test1' as varchar),cast('test2' as varchar) , 12345, now(), 10000,30, 8, 400,now(),now(),now());

and I got another error; 

ERROR:  function cargo.insertinvoice(integer, integer, integer, character varying, character varying, integer, timestamp with time zone, integer, integer, integer, integer, timestamp with time zone, timestamp with time zone, timestamp with time zone) does not exist

LINE 1: select * from cargo.insertinvoice(1013, 10, 44,cast('test1' ...

                      ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

It seems nothing changed. 

Yours sincerely,

Gunce 

2017-03-06 15:00 GMT+03:00 Günce Kaya <guncekaya14@xxxxxxxxx>:

Hi Charles,

Thank you for your response. I used pg_typeof() function to show parameters type. 

select pg_typeof('asdasdasd');

result -> "unknown"

but I can use this parameter without custom function as successfully and using cast is doesn't work to get result as successful.

do you have any advice for that?

Regards,

Gunce

2017-03-06 14:51 GMT+03:00 Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>:

Hello

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Günce Kaya
Sent: Montag, 6. März 2017 12:36
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Fwd: parameter type is unknown error

 

Hi all,

 

I created a dummy table and related function that include insert script. When I execute the function I'm getting error like bellow;

 

ERROR:  function cargo.insertinvoice(integer, integer, integer, unknown, unknown, unknown, integer, timestamp with time zone, integer, integer, money, timestamp with time zone, timestamp with time zone, timestamp with time zone) does not exist

LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...

                      ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

 

Table's create script as bellow;

 

CREATE TABLE cargo.invoice

(

  id bigserial NOT NULL,

  orderid integer NOT NULL,

  cargoid integer NOT NULL,

  invoiceowner integer NOT NULL,

  invoiceaddress character(250) NOT NULL,

  receiverfname character varying(50) NOT NULL,

  receiverlname character varying(50) NOT NULL,

  receiverphone integer NOT NULL,

  sendingdatetime timestamp without time zone DEFAULT now(),

  distance real NOT NULL,

  weight numeric NOT NULL,

  addedtax numeric NOT NULL DEFAULT 8,

  invoiceamount money DEFAULT 0,

  cargocreateddate date,

  cargoupdateddate timestamp without time zone,

  cargocancelled timestamp without time zone);

 

The function that content insert script to cargo.invoice table is following;

 

create or replace function cargo.insertinvoice (forderid integer, fcargoid integer, finvoiceowner integer, finvoiceaddress character, freceiverfname character varying, freceiverlname character varying, freceiverphone integer, fsendingdatetime timestamp without time zone, fdistance real, fweight numeric,  finvoiceamount money, fcargocreateddate date, fcargoupdateddate timestamp, fcargocancelled timestamp without time zone) returns numeric as $$

 

declare v_id bigint;

 

begin

 

insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddress, receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values(forded, fcargoid, finvoiceowner, finvoiceaddress, freceiverfname,freceiverlname,freceiverphone, fsendingdatetime, fdistance, fweight, faddedtax, finvoiceamount, fcargocreateddate, fcargoupdateddate, fcargocancelled);

 

select max(id) into v_id from cargo.invoice;

 

return v_id;

 

end;

 

$$ language plpgsql;

 

 

So, when I execute the function like;

 

select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2', 12345, now(), 10000,30, 400, now(), now(), now());

 

I'm getting error as you can see in second paragraph even I use cast for three "unknown" parameter in insert script which is in function , I can not overcome with this issue so I still get same error.  If I use only following script to insert values to cargo.invoice table,

 

insert into cargo.invoice ( orderid, cargoid, invoiceowner, invoiceaddress, receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values( 1012, 10, 44, 'asdasdasd','xx', 'xxx', 12345, now(), 10000,30,8,400,now(),now(),now());

 

I can insert data successfully. Do you have any idea about that? 

 

One or more parameter don’t have the expected data type. You should check them in order to find out which ones are giving trouble.

In the short you may try to cast your calls to now() to timezone without time stamp: now()::timestamp.

 

The function now() returns:

 

db=> select pg_typeof(now());

        pg_typeof

--------------------------

timestamp with time zone

(1 row)

 

And this is different from without time zone.

 

Hope this helps.

Bye

Charles

 

Any help would be appreciated.

 

Regards,

 

--

Gunce Kaya

--

Gunce Kaya

--

Gunce Kaya