From: "Charles Clavadetscher" <clavadetscher@swisspug.org>
To: =?utf-8?Q?'G=C3=BCnce_Kaya'?= <guncekaya14@gmail.com>
Cc: <pgsql-general@postgresql.org>
References: 
 <CAAV2-mXSDs-hHVyeE7XWd4oSqM7MXQ7o2T+kyYBE2kgh6KmYJQ@mail.gmail.com>
 <CAAV2-mX-Kj377sA4bv_Gr+Zi2ZFynPtWiyUWSzSpRO=RxN+eYw@mail.gmail.com>
 <000401d29670$10e9d870$32bd8950$@swisspug.org>
 <CAAV2-mXrrn8NaF3X8VR0P7kQLA9eUzphe1Vv9acgon_S4VKsgw@mail.gmail.com>
 <001b01d29674$4b8c62f0$e2a528d0$@swisspug.org>
 <CAAV2-mX-+TD6Cg8HQL=YynH4Wp1otuqDY6SB0F-pWNVvBS74dg@mail.gmail.com>
In-Reply-To: 
 <CAAV2-mX-+TD6Cg8HQL=YynH4Wp1otuqDY6SB0F-pWNVvBS74dg@mail.gmail.com>
Subject: Re: Fwd: parameter type is unknown error
Date: Mon, 6 Mar 2017 13:51:54 +0100
Message-ID: <003e01d29678$74b708c0$5e251a40$@swisspug.org>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: 
 AQEC2VxWTKByKmP5AyD42dQaO6xDmAJ2NHrZAgorHWkCTVCkHALx+/p6AXEOWm+izbedAA==
Content-Language: de
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

Hello G=C3=BCnce

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@post=
gresql.org] On Behalf Of G=C3=BCnce Kaya
> Sent: Montag, 6. M=C3=A4rz 2017 13:37
> To: Charles Clavadetscher <clavadetscher@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Fwd: parameter type is unknown error
>=20
> Hi Charles,
>=20
> 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.

This works:

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

create or replace function cargo.insertinvoice (forderid integer,
                                                fcargoid integer,
                                                finvoiceowner integer,
                                                finvoiceaddress varchar(250=
),
                                                freceiverfname varchar,
                                                freceiverlname varchar,
                                                freceiverphone integer,
                                                fsendingdatetime timestamp =
without time zone,
                                                fdistance real,
                                                faddedtax numeric,
                                                fweight numeric,
                                                finvoiceamount money,
                                                fcargocreateddate date,
                                                fcargoupdateddate timestamp,
                                                fcargocancelled timestamp w=
ithout 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)=20
values(forderid,
       fcargoid,
       finvoiceowner,
       finvoiceaddress,
       freceiverfname,
       freceiverlname,
       freceiverphone,
       fsendingdatetime,
       fdistance,
       fweight,
       faddedtax,
       finvoiceamount,
       fcargocreateddate,
       fcargoupdateddate,
       fcargocancelled)
returning id into v_id;
return v_id;
end;
$$ language plpgsql;

select * from cargo.insertinvoice(1013,
                                  10,
                                  44,
                                  'Wherever'::varchar(250),
                                  'test1'::varchar,
                                  'test2'::varchar,
                                  12345,
                                  now()::timestamp,
                                  10000.30::real,
                                  1000::numeric,
                                  8::numeric,
                                  400::money,
                                  now()::date,
                                  now()::timestamp,
                                  now()::timestamp);

As Tom pointed out there were many problems:
- List of parameter not matching the table.
- List of parameters types not matching the list in the function call.
- Returning the id from the function.

Some of the casting may not be mandatory, but it is up to you now to check =
this.

Hope this helps.
Bye
Charles

>=20
> Regards,
>=20
> Gunce
>=20
> 2017-03-06 15:22 GMT+03:00 Charles Clavadetscher <clavadetscher@swisspug.=
org <mailto:clavadetscher@swisspug.org> >:
>=20
>=20
> 	Hello G=C3=BCnce
>=20
>=20
>=20
> 	From: pgsql-general-owner@postgresql.org <mailto:pgsql-general-owner@pos=
tgresql.org>  [mailto:pgsql-general-
> owner@postgresql.org <mailto:pgsql-general-owner@postgresql.org> ] On Beh=
alf Of G=C3=BCnce Kaya
> 	Sent: Montag, 6. M=C3=A4rz 2017 13:01
> 	To: Charles Clavadetscher <clavadetscher@swisspug.org <mailto:clavadetsc=
her@swisspug.org> >
> 	Cc: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
> 	Subject: Re: [GENERAL] Fwd: parameter type is unknown error
>=20
>=20
>=20
> 	Hi Charles,
>=20
>=20
>=20
> 	Thank you for your response. I used pg_typeof() function to show paramet=
ers type.
>=20
>=20
>=20
> 	select pg_typeof('asdasdasd');
>=20
>=20
>=20
> 	result -> "unknown"
>=20
>=20
>=20
> 	Well, a string in single quotes is not necessarily a text:
>=20
>=20
>=20
> 	db=3D> select pg_typeof('{"hello": "world"}');
>=20
> 	pg_typeof
>=20
> 	-----------
>=20
> 	unknown
>=20
> 	(1 row)
>=20
>=20
>=20
> 	db=3D> select pg_typeof('{"hello": "world"}'::varchar);
>=20
> 	     pg_typeof
>=20
> 	-------------------
>=20
> 	character varying
>=20
> 	(1 row)
>=20
>=20
>=20
> 	db=3D> select pg_typeof('{"hello": "world"}'::text);
>=20
> 	pg_typeof
>=20
> 	-----------
>=20
> 	text
>=20
> 	(1 row)
>=20
>=20
>=20
> 	db.archivedb=3D> select pg_typeof('{"hello": "world"}'::json);
>=20
> 	pg_typeof
>=20
> 	-----------
>=20
> 	json
>=20
> 	(1 row)
>=20
>=20
>=20
> 	You probably need to specify that your string is a text.
>=20
> 	Bye
>=20
> 	Charles
>=20
>=20
>=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
>=20
>=20
> 	do you have any advice for that?
>=20
>=20
>=20
> 	Regards,
>=20
>=20
>=20
> 	Gunce
>=20
>=20
>=20
> 	2017-03-06 14:51 GMT+03:00 Charles Clavadetscher <clavadetscher@swisspug=
.org
> <mailto:clavadetscher@swisspug.org> >:
>=20
> 		Hello
>=20
>=20
>=20
> 		From: pgsql-general-owner@postgresql.org <mailto:pgsql-general-owner@po=
stgresql.org>  [mailto: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 <mailto:pgsql-general@postgresql.org>
> 		Subject: [GENERAL] Fwd: parameter type is unknown error
>=20
>=20
>=20
> 		Hi all,
>=20
>=20
>=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
>=20
>=20
> 		ERROR:  function cargo.insertinvoice(integer, integer, integer, unknown=
, unknown, unknown, integer,
> timestamp with time zone, integer, integer, money, timestamp with time zo=
ne, timestamp with time zone, timestamp
> with time zone) does not exist
>=20
> 		LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...
>=20
> 		                      ^
>=20
> 		HINT:  No function matches the given name and argument types. You might=
 need to add explicit type
> casts.
>=20
>=20
>=20
> 		Table's create script as bellow;
>=20
>=20
>=20
> 		CREATE TABLE cargo.invoice
>=20
> 		(
>=20
> 		  id bigserial NOT NULL,
>=20
> 		  orderid integer NOT NULL,
>=20
> 		  cargoid integer NOT NULL,
>=20
> 		  invoiceowner integer NOT NULL,
>=20
> 		  invoiceaddress character(250) NOT NULL,
>=20
> 		  receiverfname character varying(50) NOT NULL,
>=20
> 		  receiverlname character varying(50) NOT NULL,
>=20
> 		  receiverphone integer NOT NULL,
>=20
> 		  sendingdatetime timestamp without time zone DEFAULT now(),
>=20
> 		  distance real NOT NULL,
>=20
> 		  weight numeric NOT NULL,
>=20
> 		  addedtax numeric NOT NULL DEFAULT 8,
>=20
> 		  invoiceamount money DEFAULT 0,
>=20
> 		  cargocreateddate date,
>=20
> 		  cargoupdateddate timestamp without time zone,
>=20
> 		  cargocancelled timestamp without time zone);
>=20
>=20
>=20
> 		The function that content insert script to cargo.invoice table is follo=
wing;
>=20
>=20
>=20
> 		create or replace function cargo.insertinvoice (forderid integer, fcarg=
oid integer, finvoiceowner
> integer, finvoiceaddress character, freceiverfname character varying, fre=
ceiverlname character varying,
> freceiverphone integer, fsendingdatetime timestamp without time zone, fdi=
stance real, fweight numeric,
> finvoiceamount money, fcargocreateddate date, fcargoupdateddate timestamp=
, fcargocancelled timestamp without time
> zone) returns numeric as $$
>=20
>=20
>=20
> 		declare v_id bigint;
>=20
>=20
>=20
> 		begin
>=20
>=20
>=20
> 		insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddre=
ss, 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, fcargocr=
eateddate, fcargoupdateddate,
> fcargocancelled);
>=20
>=20
>=20
> 		select max(id) into v_id from cargo.invoice;
>=20
>=20
>=20
> 		return v_id;
>=20
>=20
>=20
> 		end;
>=20
>=20
>=20
> 		$$ language plpgsql;
>=20
>=20
>=20
>=20
>=20
> 		So, when I execute the function like;
>=20
>=20
>=20
> 		select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2',=
 12345, now(), 10000,30, 400,
> now(), now(), now());
>=20
>=20
>=20
> 		I'm getting error as you can see in second paragraph even I use cast fo=
r three "unknown" parameter in
> insert script which is in function , I can not overcome with this issue s=
o I still get same error.  If I use only
> following script to insert values to cargo.invoice table,
>=20
>=20
>=20
> 		insert into cargo.invoice ( orderid, cargoid, invoiceowner, invoiceaddr=
ess, 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
>=20
>=20
> 		I can insert data successfully. Do you have any idea about that?
>=20
>=20
>=20
> 		One or more parameter don=E2=80=99t have the expected data type. You sh=
ould check them in order to find out
> which ones are giving trouble.
>=20
> 		In the short you may try to cast your calls to now() to timezone withou=
t time stamp: now()::timestamp.
>=20
>=20
>=20
> 		The function now() returns:
>=20
>=20
>=20
> 		db=3D> select pg_typeof(now());
>=20
> 		        pg_typeof
>=20
> 		--------------------------
>=20
> 		timestamp with time zone
>=20
> 		(1 row)
>=20
>=20
>=20
> 		And this is different from without time zone.
>=20
>=20
>=20
> 		Hope this helps.
>=20
> 		Bye
>=20
> 		Charles
>=20
>=20
>=20
> 		Any help would be appreciated.
>=20
>=20
>=20
> 		Regards,
>=20
>=20
>=20
> 		--
>=20
> 		Gunce Kaya
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> 	--
>=20
> 	Gunce Kaya
>=20
>=20
>=20
>=20
> --
>=20
> Gunce Kaya


--=20
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general