Re: 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"

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(at)swisspug(dot)org
>:

> Hello
>
>
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Günce Kaya
> *Sent:* Montag, 6. März 2017 12:36
> *To:* pgsql-general(at)postgresql(dot)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