Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ckrhU-0001Ud-Ts for pgsql-general@arkaria.postgresql.org; Mon, 06 Mar 2017 12:25:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ckrhU-0005R3-Ez for pgsql-general@arkaria.postgresql.org; Mon, 06 Mar 2017 12:25:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ckrev-0005Iw-BJ for pgsql-general@postgresql.org; Mon, 06 Mar 2017 12:22:37 +0000 Received: from 9v6.net-wizard.org ([2a01:4f8:172:2a5c::9] helo=aragorn.net-wizard.org) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ckrer-00020M-IO for pgsql-general@postgresql.org; Mon, 06 Mar 2017 12:22:36 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.net-wizard.org (Postfix) with ESMTP id BE65782260; Mon, 6 Mar 2017 13:22:32 +0100 (CET) X-Virus-Scanned: Gentoo amavisd-new at aragorn Received: from aragorn.net-wizard.org ([127.0.0.1]) by localhost (aragorn.net-wizard.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rIf7y57wxh6q; Mon, 6 Mar 2017 13:22:17 +0100 (CET) Received: from MTEC0211 (mtec-lee-dock-2-133.ethz.ch [129.132.6.133]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA384 (256/256 bits)) (No client certificate requested) (Authenticated sender: clavadetscher@swisspug.org) by aragorn.net-wizard.org (Postfix) with ESMTPSA id 7EF3F82246; Mon, 6 Mar 2017 13:22:13 +0100 (CET) From: "Charles Clavadetscher" To: =?utf-8?Q?'G=C3=BCnce_Kaya'?= Cc: References: <000401d29670$10e9d870$32bd8950$@swisspug.org> In-Reply-To: Subject: Re: Fwd: parameter type is unknown error Date: Mon, 6 Mar 2017 13:22:07 +0100 Message-ID: <001b01d29674$4b8c62f0$e2a528d0$@swisspug.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001C_01D2967C.AD533BF0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQEC2VxWTKByKmP5AyD42dQaO6xDmAJ2NHrZAgorHWkCTVCkHKLwx3qQ Content-Language: de X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_001C_01D2967C.AD533BF0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hello G=C3=BCnce =20 From: pgsql-general-owner@postgresql.org = [mailto:pgsql-general-owner@postgresql.org] On Behalf Of G=C3=BCnce Kaya Sent: Montag, 6. M=C3=A4rz 2017 13:01 To: Charles Clavadetscher Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Fwd: parameter type is unknown error =20 Hi Charles, =20 Thank you for your response. I used pg_typeof() function to show = parameters type.=20 =20 select pg_typeof('asdasdasd'); =20 result -> "unknown" =20 Well, a string in single quotes is not necessarily a text: =20 db=3D> select pg_typeof('{"hello": "world"}'); pg_typeof ----------- unknown (1 row) =20 db=3D> select pg_typeof('{"hello": "world"}'::varchar); pg_typeof ------------------- character varying (1 row) =20 db=3D> select pg_typeof('{"hello": "world"}'::text); pg_typeof ----------- text (1 row) =20 db.archivedb=3D> select pg_typeof('{"hello": "world"}'::json); pg_typeof ----------- json (1 row) =20 You probably need to specify that your string is a text. Bye Charles =20 but I can use this parameter without custom function as successfully and = using cast is doesn't work to get result as successful. =20 do you have any advice for that? =20 Regards, =20 Gunce =20 2017-03-06 14:51 GMT+03:00 Charles Clavadetscher = >: Hello =20 From: pgsql-general-owner@postgresql.org = = [mailto:pgsql-general-owner@postgresql.org = ] On Behalf Of G=C3=BCnce = Kaya Sent: Montag, 6. M=C3=A4rz 2017 12:36 To: pgsql-general@postgresql.org =20 Subject: [GENERAL] Fwd: parameter type is unknown error =20 Hi all, =20 I created a dummy table and related function that include insert script. = When I execute the function I'm getting error like bellow; =20 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. =20 Table's create script as bellow; =20 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); =20 The function that content insert script to cargo.invoice table is = following; =20 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 $$ =20 declare v_id bigint; =20 begin =20 insert into cargo.invoice (orderid, cargoid, invoiceowner, = invoiceaddress, receiverfname, receiverlname, receiverphone, = sendingdatetime, distance, weight, addedtax, invoiceamount, = cargocreateddate, cargoupdateddate, cargocancelled)=20 values(forded, fcargoid, finvoiceowner, finvoiceaddress, = freceiverfname,freceiverlname,freceiverphone, fsendingdatetime, = fdistance, fweight, faddedtax, finvoiceamount, fcargocreateddate, = fcargoupdateddate, fcargocancelled); =20 select max(id) into v_id from cargo.invoice; =20 return v_id; =20 end; =20 $$ language plpgsql; =20 =20 So, when I execute the function like; =20 select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2', = 12345, now(), 10000,30, 400, now(), now(), now()); =20 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, =20 insert into cargo.invoice ( orderid, cargoid, invoiceowner, = invoiceaddress, receiverfname, receiverlname, receiverphone, = sendingdatetime, distance, weight, addedtax, invoiceamount, = cargocreateddate, cargoupdateddate, cargocancelled)=20 values( 1012, 10, 44, 'asdasdasd','xx', 'xxx', 12345, now(), = 10000,30,8,400,now(),now(),now()); =20 I can insert data successfully. Do you have any idea about that?=20 =20 One or more parameter don=E2=80=99t 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. =20 The function now() returns: =20 db=3D> select pg_typeof(now()); pg_typeof -------------------------- timestamp with time zone (1 row) =20 And this is different from without time zone. =20 Hope this helps. Bye Charles =20 Any help would be appreciated. =20 Regards, =20 --=20 Gunce Kaya =20 --=20 Gunce Kaya ------=_NextPart_000_001C_01D2967C.AD533BF0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hello = G=C3=BCnce

 

From:<= /b> = pgsql-general-owner@postgresql.org = [mailto:pgsql-general-owner@postgresql.org] On Behalf Of = G=C3=BCnce Kaya
Sent: Montag, 6. M=C3=A4rz 2017 = 13:01
To: Charles Clavadetscher = <clavadetscher@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=3D> select pg_typeof('{"hello": = "world"}');

= pg_typeof

-----------

= unknown

(1 = row)

 

db=3D> select pg_typeof('{"hello": = "world"}'::varchar);

=C2=A0=C2=A0=C2=A0=C2=A0 = pg_typeof

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

= character varying

(1 = row)

 

db=3D> select pg_typeof('{"hello": = "world"}'::text);

= pg_typeof

-----------

= text

(1 = row)

 

db.archivedb=3D> 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@swisspug.org>:

Hello

 

From:<= /b> pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf = Of G=C3=BCnce Kaya
Sent: Montag, 6. M=C3=A4rz 2017 = 12:36
To: pgsql-general@postgresql.org
Subject: = [GENERAL] Fwd: parameter type is unknown = error

 <= /o:p>

Hi = all,

 <= /o:p>

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

 <= /o:p>

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.

 <= /o:p>

Table's = create script as bellow;

 <= /o:p>

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);

 <= /o:p>

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

 <= /o:p>

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 $$

 <= /o:p>

declare v_id = bigint;

 <= /o:p>

begin

 <= /o:p>

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);

 <= /o:p>

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

 <= /o:p>

return = v_id;

 <= /o:p>

end;

 <= /o:p>

$$ language = plpgsql;

 <= /o:p>

 <= /o:p>

So, when I = execute the function like;

 <= /o:p>

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

 <= /o:p>

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,

 <= /o:p>

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());

 <= /o:p>

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

 

One or more parameter don=E2=80=99t 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=3D> 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.

 <= /o:p>

Regards,

 <= /o:p>

-- =

Gunce = Kaya



 

-- =

Gunce = Kaya

------=_NextPart_000_001C_01D2967C.AD533BF0--