Stored Proc error after modifying field type

[Együd Csaba]

Hi,
I've a problem with some of my stored procs. My config is: RH7.1, Postgres
7.3.2

I had converted a few fields of a few tables from one type to another and
after this I made all the necessary changes on the functions and recreated
all my types and functions.
It seemd to be all right, but the newly created functions won't work
anymore.

The error message says after running one of them:
-----------------------------------------------------------------------
ERROR: Function addincominginvoice(integer, integer, "unknown", "unknown",
"unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown",
double precision) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
-----------------------------------------------------------------------

This error rises only with newly created functions, old functions are workig
fine.

Should anybody suggest me something to avoid this problem.

Thanks a lot,

-- Csaba


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

=?iso-8859-1?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:

ERROR: Function addincominginvoice(integer, integer, "unknown", "unknown",
"unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown",
double precision) does not exist
Unable to identify a function that satisfies the given argument types

The error message may be misleading you --- that same error will be
reported when there is no possible match in pg_proc, and when there
are multiple matches and the system can't figure out which to pick.
(I plan to try to make this better in 7.4...) Given all the unknowns
you've got there, multiple matches seems like a likely problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Együd Csaba]

Hi Tom,
querying my functions results this:

alumil=# \df addincominginvoice
List of functions
Result data type | Schema | Name |
Argument data types
------------------+--------+--------------------+---------------------------
---------------------------------------
integer | public | addincominginvoice | integer, integer, text,
text, text, text, integer, text, text, text, text, numeric
(1 row)

This means that I have exactly one addincominginvoice function, in addition
with the appropriate parameter list.

----------------------------------------------------------------------------
--------------------------------------
Calling this procedure from the console, it goes well.
alumil=# select addincominginvoice (2, -1, 'xxxx', '2003.07.01',
'2003.07.01', 'DDDD', 25, '2003.09.01', 'f', ' ', 'f', 2332.334);
addincominginvoice
--------------------
10
----------------------------------------------------------------------------
--------------------------------------

Calling it from the win32 client it fails. (The client is a Delphi 7
application using BDE)
The traffic between the client and the backend is the following:
----------------------------------------------------------------------------
--------------------------------------
1 17:59:12 SQL Prepare: PostgreSQL - { call "addincominginvoice"
(?,?,?,?,?,?,?,?,?,?,?,?)}
2 17:59:12 SQL Misc: PostgreSQL - Set stored procedure on or off
3 17:59:12 SQL Data In: PostgreSQL - Param = 1, Name = PartnerID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 19
4 17:59:12 SQL Data In: PostgreSQL - Param = 2, Name = OrderNumber,
Type = fldINT32, Precision = 0, Scale = 0, Data = -1
5 17:59:12 SQL Data In: PostgreSQL - Param = 3, Name = SN, Type =
fldZSTRING, Precision = 12, Scale = 0, Data = xxxxxxxxxxxx
6 17:59:12 SQL Data In: PostgreSQL - Param = 4, Name = PerformanceDat
e, Type = fldZSTRING, Precision = 10, Scale = 0, Data = 2003.07.02
7 17:59:12 SQL Data In: PostgreSQL - Param = 5, Name = IssuingDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data = 2003.07.02
8 17:59:12 SQL Data In: PostgreSQL - Param = 6, Name = LedgerAccount,
Type = fldZSTRING, Precision = 7, Scale = 0, Data = YYYYYYY
9 17:59:12 SQL Data In: PostgreSQL - Param = 7, Name = UserID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 25
10 17:59:12 SQL Data In: PostgreSQL - Param = 8, Name =
PaymentDeadLine, Type = fldZSTRING, Precision = 10, Scale = 0, Data =
2003.07.02
11 17:59:12 SQL Data In: PostgreSQL - Param = 9, Name = Payed, Type =
fldZSTRING, Precision = 1, Scale = 0, Data = f
12 17:59:12 SQL Data In: PostgreSQL - Param = 10, Name = PayedDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data =
13 17:59:12 SQL Data In: PostgreSQL - Param = 11, Name = NoStock, Type
= fldZSTRING, Precision = 1, Scale = 0, Data = f
14 17:59:12 SQL Data In: PostgreSQL - Param = 12, Name = GrossValue,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 0.000000
15 17:59:12 SQL Execute: PostgreSQL - { call "addincominginvoice"
(:PartnerID,:OrderNumber,:SN,:PerformanceDate,:Iss uingDate,:LedgerAccount,:U
serID,:PaymentDeadLine,:Payed,:PayedDate,:NoStock, :GrossValue)}
16 17:59:12 SQL Vendor: ODBC - SQLAllocStmt
17 17:59:12 SQL Vendor: ODBC - SQLBindParameter
18 17:59:12 SQL Vendor: ODBC - SQLBindParameter
19 17:59:12 SQL Vendor: ODBC - SQLBindParameter
20 17:59:12 SQL Vendor: ODBC - SQLBindParameter
21 17:59:12 SQL Vendor: ODBC - SQLBindParameter
22 17:59:12 SQL Vendor: ODBC - SQLBindParameter
23 17:59:12 SQL Vendor: ODBC - SQLBindParameter
24 17:59:12 SQL Vendor: ODBC - SQLBindParameter
25 17:59:12 SQL Vendor: ODBC - SQLBindParameter
26 17:59:12 SQL Vendor: ODBC - SQLBindParameter
27 17:59:12 SQL Vendor: ODBC - SQLBindParameter
28 17:59:12 SQL Vendor: ODBC - SQLBindParameter
29 17:59:12 SQL Vendor: ODBC - SQLExecDirect
30 17:59:12 SQL Vendor: ODBC - SQLError
31 17:59:12 SQL Error: PostgreSQL - ERROR: Function
addincominginvoice(integer, integer, "unknown", "unknown", "unknown",
"unknown", integer, "unknown", "unknown", "unknown", "unknown", double
precision) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
32 17:59:12 SQL Vendor: ODBC - SQLError
33 17:59:12 SQL Stmt: PostgreSQL - Reset
34 17:59:12 SQL Vendor: ODBC - SQLFreeStmt

----------------------------------------------------------------------------
--------------------------------------
This trace is provided by the psqlODBC driver.
Can you find anything in this log, which can cause the error?

Thank you,
-- Csaba

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Együd Csaba" <cs*****@freemail.hu>
Cc: <pg***********@postgresql.org>
Sent: Wednesday, July 02, 2003 5:41 PM
Subject: Re: [GENERAL] Stored Proc error after modifying field type

=?iso-8859-1?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:

ERROR: Function addincominginvoice(integer, integer, "unknown", "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown", double precision) does not exist
Unable to identify a function that satisfies the given argument types

The error message may be misleading you --- that same error will be
reported when there is no possible match in pg_proc, and when there
are multiple matches and the system can't figure out which to pick.
(I plan to try to make this better in 7.4...) Given all the unknowns
you've got there, multiple matches seems like a likely problem.

regards, tom lane

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

=?iso-8859-2?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:

alumil=# \df addincominginvoice
List of functions
Result data type | Schema | Name |
Argument data types
------------------+--------+--------------------+---------------------------
---------------------------------------
integer | public | addincominginvoice | integer, integer, text,
text, text, text, integer, text, text, text, text, numeric
(1 row) 31 17:59:12 SQL Error: PostgreSQL - ERROR: Function
addincominginvoice(integer, integer, "unknown", "unknown", "unknown",
"unknown", integer, "unknown", "unknown", "unknown", "unknown", double
precision) does not exist

IIRC, there's no implicit cast from double precision to numeric, only
the other direction. You'll need to do something to persuade the ODBC
driver to select numeric not float as the parameter type. This is
outside my field --- you might get some help in pgsql-odbc.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Együd Csaba]

Sorry for posting many times!
-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Együd Csaba]

Thank you Tom,
I'll check it out. But I must say that I have many other functions using
numeric fields and working well.
This error has been rising up since I converted my fields from double
precision to numeric.
May be the problem is in my ODBC settings but I'm sceptic a bit about this.
I had set the
"Int8 As numeric" option before converting my fields.

Anyway, I subscript pgsql-odbc list.

Thank you very much.

-- Csaba

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Együd Csaba" <cs*****@freemail.hu>
Cc: <pg***********@postgresql.org>
Sent: Wednesday, July 02, 2003 6:23 PM
Subject: Re: [GENERAL] Stored Proc error after modifying field type

=?iso-8859-2?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:

alumil=# \df addincominginvoice
List of functions Result data type | Schema | Name |
Argument data types

------------------+--------+--------------------+-------------------------

--

---------------------------------------
integer | public | addincominginvoice | integer, integer, text, text, text, text, integer, text, text, text, text, numeric
(1 row)

31 17:59:12 SQL Error: PostgreSQL - ERROR: Function
addincominginvoice(integer, integer, "unknown", "unknown", "unknown",
"unknown", integer, "unknown", "unknown", "unknown", "unknown", double
precision) does not exist

IIRC, there's no implicit cast from double precision to numeric, only
the other direction. You'll need to do something to persuade the ODBC
driver to select numeric not float as the parameter type. This is
outside my field --- you might get some help in pgsql-odbc.

regards, tom lane

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Együd Csaba]

Tom,
what do you think about the folowing: I updated the server field of my DSN
to point to the postgres runniong under cygwin on the localhost. The error
does not appear at all.
----------------------------------
1 06:29:42 SQL Prepare: PostgreSQL - { call "addincominginvoice"
(?,?,?,?,?,?,?,?,?,?,?,?)}
2 06:29:42 SQL Misc: PostgreSQL - Set stored procedure on or off
3 06:29:42 SQL Data In: PostgreSQL - Param = 1, Name = PartnerID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 3
4 06:29:42 SQL Data In: PostgreSQL - Param = 2, Name = OrderNumber,
Type = fldINT32, Precision = 0, Scale = 0, Data = -1
5 06:29:42 SQL Data In: PostgreSQL - Param = 3, Name = SN, Type =
fldZSTRING, Precision = 20, Scale = 0, Data = AAAAAAAAAAAAAAAAAAAA
6 06:29:42 SQL Data In: PostgreSQL - Param = 4, Name =
PerformanceDate, Type = fldZSTRING, Precision = 10, Scale = 0, Data =
2003.07.03
7 06:29:42 SQL Data In: PostgreSQL - Param = 5, Name = IssuingDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data = 2003.07.03
8 06:29:42 SQL Data In: PostgreSQL - Param = 6, Name = LedgerAccount,
Type = fldZSTRING, Precision = 10, Scale = 0, Data = BBBBBBBBBB
9 06:29:42 SQL Data In: PostgreSQL - Param = 7, Name = UserID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 25
10 06:29:42 SQL Data In: PostgreSQL - Param = 8, Name =
PaymentDeadLine, Type = fldZSTRING, Precision = 10, Scale = 0, Data =
2003.07.03
11 06:29:42 SQL Data In: PostgreSQL - Param = 9, Name = Payed, Type =
fldZSTRING, Precision = 1, Scale = 0, Data = f
12 06:29:42 SQL Data In: PostgreSQL - Param = 10, Name = PayedDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data =
13 06:29:42 SQL Data In: PostgreSQL - Param = 11, Name = NoStock, Type
= fldZSTRING, Precision = 1, Scale = 0, Data = f
14 06:29:42 SQL Data In: PostgreSQL - Param = 12, Name = GrossValue,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 0.000000
15 06:29:42 SQL Execute: PostgreSQL - { call "addincominginvoice"
(:PartnerID,:OrderNumber,:SN,:PerformanceDate,:Iss uingDate,:LedgerAccount,:U
serID,:PaymentDeadLine,:Payed,:PayedDate,:NoStock, :GrossValue)}
16 06:29:42 SQL Vendor: ODBC - SQLAllocStmt
17 06:29:42 SQL Vendor: ODBC - SQLBindParameter
18 06:29:42 SQL Vendor: ODBC - SQLBindParameter
19 06:29:42 SQL Vendor: ODBC - SQLBindParameter
20 06:29:42 SQL Vendor: ODBC - SQLBindParameter
21 06:29:42 SQL Vendor: ODBC - SQLBindParameter
22 06:29:42 SQL Vendor: ODBC - SQLBindParameter
23 06:29:42 SQL Vendor: ODBC - SQLBindParameter
24 06:29:42 SQL Vendor: ODBC - SQLBindParameter
25 06:29:42 SQL Vendor: ODBC - SQLBindParameter
26 06:29:42 SQL Vendor: ODBC - SQLBindParameter
27 06:29:42 SQL Vendor: ODBC - SQLBindParameter
28 06:29:42 SQL Vendor: ODBC - SQLBindParameter
29 06:29:42 SQL Vendor: ODBC - SQLExecDirect
30 06:29:42 SQL Vendor: ODBC - SQLNumResultCols
31 06:29:42 SQL Vendor: ODBC - SQLDescribeCol
32 06:29:42 SQL Misc: PostgreSQL - Set rowset size
33 06:29:42 SQL Vendor: ODBC - SQLBindCol
34 06:29:42 SQL Stmt: PostgreSQL - Fetch
35 06:29:42 SQL Vendor: ODBC - SQLSetStmtOption
36 06:29:42 SQL Vendor: ODBC - SQLExtendedFetch
37 06:29:42 SQL Data Out: PostgreSQL - Column = 1, Name =
addincominginvoice, Type = fldINT32, Precision = 10, Scale = 0, Data = 18
38 06:29:42 SQL Stmt: PostgreSQL - Reset
39 06:29:42 SQL Vendor: ODBC - SQLFreeStmt
40 06:29:42 SQL Stmt: PostgreSQL - Close
41 06:29:42 SQL Stmt: PostgreSQL - Reset
42 06:29:42 SQL Vendor: ODBC - SQLFreeStmt
43 06:29:42 SQL Stmt: PostgreSQL - Close
44 06:29:42 SQL Prepare: PostgreSQL - select count(*) as count from
t_incoming_invoices;
----------------------------------
But the following function fails:
----------------------------------
1 06:33:06 SQL Prepare: PostgreSQL - { call "addinstalment" (?,?,?)}
2 06:33:06 SQL Misc: PostgreSQL - Set stored procedure on or off
3 06:33:06 SQL Data In: PostgreSQL - Param = 1, Name = InvoiceID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 35
4 06:33:06 SQL Data In: PostgreSQL - Param = 2, Name = PayDate, Type
= fldZSTRING, Precision = 10, Scale = 0, Data = 2003.04.18
5 06:33:06 SQL Data In: PostgreSQL - Param = 3, Name = Instalment,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 1000.000000
6 06:33:06 SQL Execute: PostgreSQL - { call "addinstalment"
(:InvoiceID,:PayDate,:Instalment)}
7 06:33:06 SQL Vendor: ODBC - SQLAllocStmt
8 06:33:06 SQL Vendor: ODBC - SQLBindParameter
9 06:33:06 SQL Vendor: ODBC - SQLBindParameter
10 06:33:06 SQL Vendor: ODBC - SQLBindParameter
11 06:33:06 SQL Vendor: ODBC - SQLExecDirect
12 06:33:06 SQL Vendor: ODBC - SQLError
13 06:33:06 SQL Error: PostgreSQL - ERROR: Function
addinstalment(integer, "unknown", double precision) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
14 06:33:06 SQL Vendor: ODBC - SQLError
15 06:33:06 SQL Stmt: PostgreSQL - Reset
16 06:33:06 SQL Vendor: ODBC - SQLFreeStmt
17 06:33:09 SQL Stmt: PostgreSQL - Close
----------------------------------
alumil=# \df addinstalment
List of functions
Result data type | Schema | Name | Argument data types
------------------+--------+---------------+------------------------
integer | public | addinstalment | integer, text, numeric
(1 row)
----------------------------------

Do you think yet the ODBC can be balmed. I don't know what to think.

-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Együd Csaba]

Hi,
I got out of this problem, but I'm not sure I found the best way.
I changed the parameter types of my functions from decimal to text. It
results, that I can send string values for decimal fields. This works well,
but I don't know if it is leading to other problems or not.

Thank you,

-- Csaba


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org