Hi Charles, You are right about parameter types. When I use function with cast as you mentioned, I don't see "unknown". I see data types. but still doesn't insert with my custom function. It's really weird cause I did same thing for different function and It works.
Regards, Gunce 2017-03-06 15:22 GMT+03:00 Charles Clavadetscher <clavadetsc...@swisspug.org >: > Hello Günce > > > > *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@ > postgresql.org] *On Behalf Of *Günce Kaya > *Sent:* Montag, 6. März 2017 13:01 > *To:* Charles Clavadetscher <clavadetsc...@swisspug.org> > *Cc:* pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] Fwd: parameter type is unknown error > > > > Hi Charles, > > > > Thank you for your response. I used pg_typeof() function to show > parameters type. > > > > select pg_typeof('asdasdasd'); > > > > result -> "unknown" > > > > Well, a string in single quotes is not necessarily a text: > > > > db=> select pg_typeof('{"hello": "world"}'); > > pg_typeof > > ----------- > > unknown > > (1 row) > > > > db=> select pg_typeof('{"hello": "world"}'::varchar); > > pg_typeof > > ------------------- > > character varying > > (1 row) > > > > db=> select pg_typeof('{"hello": "world"}'::text); > > pg_typeof > > ----------- > > text > > (1 row) > > > > db.archivedb=> select pg_typeof('{"hello": "world"}'::json); > > pg_typeof > > ----------- > > json > > (1 row) > > > > You probably need to specify that your string is a text. > > Bye > > Charles > > > > 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 < > clavadetsc...@swisspug.org>: > > Hello > > > > *From:* pgsql-general-ow...@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@postgresql.org > *Subject:* [GENERAL] 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
