From: | Günce Kaya <guncekaya14(at)gmail(dot)com> |
---|---|
To: | Charles Clavadetscher <clavadetscher(at)swisspug(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Fwd: parameter type is unknown error |
Date: | 2017-03-06 12:36:56 |
Message-ID: | CAAV2-mX-+TD6Cg8HQL=YynH4Wp1otuqDY6SB0F-pWNVvBS74dg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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 <clavadetscher(at)swisspug(dot)org
>:
> Hello Günce
>
>
>
> *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 13:01
> *To:* Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
> *Cc:* pgsql-general(at)postgresql(dot)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 <
> 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
>
--
Gunce Kaya
From | Date | Subject | |
---|---|---|---|
Next Message | Charles Clavadetscher | 2017-03-06 12:51:54 | Re: Fwd: parameter type is unknown error |
Previous Message | Tom Lane | 2017-03-06 12:36:25 | Re: Fwd: parameter type is unknown error |
From | Date | Subject | |
---|---|---|---|
Next Message | Charles Clavadetscher | 2017-03-06 12:51:54 | Re: Fwd: parameter type is unknown error |
Previous Message | Tom Lane | 2017-03-06 12:36:25 | Re: Fwd: parameter type is unknown error |