plpgsql function errors
Hi Everyone -
I am new to this list and although I have been using postgresql on and
off for about a year now. I am trying to develop a webapp using perl
and cgi with postgresql 7.4.6 as a backend database. One of the things
I need is to create a transactions table that will record a row of data
and then return the transaction id to the caller. I have decided to
implement this piece of the webapp as a stored procedure in pl/pgsql.
The following is the source code...
/* Function Name: trx_id
Programmer: Juan Casero
Date: 02/02/05
Description: This function is part of the customer_service database.
It is designed to grab an exclusive lock on
a table insert a single record and return the transaction id (trx_id)
of the inserted record to the caller. This
function avoids problems with concurrent access to the transactions
table. The trx_id column is a serial data
type and as such is automatically incremented by the server when a
row is inserted into the transactions table. I
lock the table so I can ensure that the function returns the trx_id
of the row inserted during the same call to the
function.
*/
CREATE OR REPLACE FUNCTION
trx_id(transactions.customer_id%TYPE,transactions.store%TYPE,transaction
s.trx_typeTYPE,transactions.trx_date%TYPE,transactions.reissue%TYPE,tran
sactions.receipt
TYPE,transactions.purch_loc%TYPE,transactions.purch_date%TYPE,transactio
ns.associate%TYPE) RETURNS transactions.trx_id%TYPE AS '
DECLARE
p_customer_id ALIAS FOR $1;
p_store ALIAS FOR $2;
p_trx_type ALIAS FOR $3;
p_trx_date ALIAS FOR $4;
p_reissue ALIAS FOR $5;
p_receipt ALIAS FOR $6;
p_purchloc ALIAS FOR $7;
p_purchdate ALIAS FOR $8;
p_associate ALIAS FOR $9;
p_trx_id transactions.trx_id%TYPE;
BEGIN
LOCK TABLE transactions IN ACCESS EXCLUSIVE MODE;
INSERT INTO transactions
(customer_id,store,trx_type,trx_date,reissue,receipt,purch_loc,purch_dat
e,associate) VALUES
(''p_customer_id'',p_store,''p_trx_type'',''p_trx_date'',''p_reissue'','
'p_receipt'',''p_purch_loc'',''p_purch_date'',''p_associate'');
SELECT INTO p_trx_id MAX(trx_id) FROM transactions;
RETURN (p_trx_id);
END;
' LANGUAGE 'plpgsql';
The following is some background information on the tables I am working
on...
Table "public.transactions"
Column | Type |
Modifiers
-------------+-----------------------+----------------------------------
--------------------------------
customer_id | character varying(60) |
store | smallint |
trx_type | character(10) |
trx_date | date |
reissue | character(1) |
receipt | character(1) |
purch_loc | character(40) |
purch_date | date |
associate | character(40) |
trx_id | integer | not null default
nextval('public.transactions_trx_id_seq'::text)
Indexes:
"transactions_pkey" primary key, btree (trx_id)
"trx_cust_stor" btree (trx_id, customer_id, store)
Foreign-key constraints:
"$1" FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON
UPDATE CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (store) REFERENCES cxbstorr(store) ON UPDATE
CASCADE ON DELETE CASCADE
customer_service=# \d customers
Table "public.customers"
Column | Type | Modifiers
-------------+------------------------+-----------
customer_id | character varying(60) | not null
fname | character varying(60) |
lname | character varying(60) |
street | character varying(120) |
city | character(40) |
state | character(2) |
zip | character(16) |
phone | character(30) |
Indexes:
"customers_pkey" primary key, btree (customer_id)
customer_service=# \d cxbstorr
Table "public.cxbstorr"
Column | Type | Modifiers
----------------+---------------+-----------
store | smallint |
company | smallint |
region | smallint |
district | smallint |
name1 | character(25) |
name2 | character(25) |
address | character(25) |
city | character(20) |
state | character(2) |
zip | character(10) |
modemno | character(36) |
manager | character(25) |
postype | character(1) |
protocol | character(6) |
fmtcode | character(6) |
primgroup | smallint |
mixmatchflag | character(1) |
primline | smallint |
secgroup | smallint |
secline | smallint |
storeid | character(15) |
hostid | character(15) |
mnt_code | character(1) |
dsdflag | character(1) |
scaletype | character(1) |
mzone | smallint |
print_rept | character(1) |
auto_print | character(1) |
addrep_for_chg | character(1) |
cost_method | character(5) |
rbx50 | character(1) |
Indexes:
"cxbstorr_store_key" unique, btree (store)
The trx_id function parses fine and loads into the database. If I
removed the entire argument list to the function and hard code an
instance of the record to inserted via the insert statment in the
function body and then call this function from a psql terminal like so
#select trx_id(); it works beautifully. The moment I put in the
argument list and then try to execute this function like this...
# select
trx_id(JUANCASERO3055128218,CREDIT,02/02/05,1,1,Aventura,02/01/05,Tom);
I get the following error message
ERROR: column "juancasero3055128218" does not exist
Now I have read the docs online and searched through the PostgreSQL
programming book by Douglas and Douglas exhaustively and I cannot find
the problem. I have tried dozens of possible permutations to this
function to try and isolate the code fragment that is causing the
problem but nothing works. I tried hard coding the argument types it
still gives me the error. I tried quoting the string arguments in
single quotes but then the server complains that there is no function
signature that matches. I made sure that all the foreign key
relationships were satisfied so the error is not about that. In fact I
even tried versions of this where I dropped all foreign references and
it still gives me the same error. Interestingly though if I only pass
this function a single parameter (customer_id) and then return the
length($1) it works! Even when passing it the same value of
JUANCASERO3055128218. I have tried everything I can think of but I am
still stumped. Please help.
Thanks,
Juan
On Fri, Feb 04, 2005 at 11:40:50AM -0600, Juan Casero (FL FLC) wrote:
Hi Everyone -
I am new to this list and although I have been using postgresql on and
off for about a year now. I am trying to develop a webapp using perl
and cgi with postgresql 7.4.6 as a backend database. One of the things
I need is to create a transactions table that will record a row of data
and then return the transaction id to the caller. I have decided to
implement this piece of the webapp as a stored procedure in pl/pgsql.
The following is the source code...
Firstly, I think you should look up the documentation for nextval(),
currval(). There is no possiblity of returning the wrong value even if
another transaction is running concurrently. And you don't need to lock
the table, which is good for performance.
<snip>
# select
trx_id(JUANCASERO3055128218,CREDIT,02/02/05,1,1,Aventura,02/01/05,Tom);I get the following error message
ERROR: column "juancasero3055128218" does not exist
You need to put those values into strings. Unless you do that it thinks
you're referring to a column named that. Try:
select trx_id('JUANCASERO3055128218','CREDIT','02/02/05',1,1,'Aventura','02/01/05','Tom');
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
I tried putting those values into strings like you describe below but
then the server bombs. e.g...
customer_service=# select
trx_id('JUANCASERO3055128218','CREDIT','02/02/05','1','1','Aventura','02
/01/05','Tom');
ERROR: function trx_id("unknown", "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
customer_service=#
It doesn't recongnize the paramter types and therefore can't match it to
any known function signature. Even when I explicitly typcast the
parameters like so...
customer_service=# select
trx_id(JUANCASERO3055128218::char,CREDIT::char,02/02/05::date,1::char,1:
:char,Aventura::char,02/01/05::date,Tom::char);
ERROR: column "juancasero3055128218" does not exist
customer_service=#
The problem persists.
I like the idea of nextval() and currval(). I was hoping to optimize
the code later. For now I just wanted to test the concept. I wanted to
start with the simplest case possible to improve my chances of success
with the procedure and optimize it later. Any ideas on why the function
call fails?
Thanks,
Juan
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Friday, February 04, 2005 1:06 PM
To: Juan Casero (FL FLC)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 11:40:50AM -0600, Juan Casero (FL FLC) wrote:
Hi Everyone -
I am new to this list and although I have been using postgresql on and
off for about a year now. I am trying to develop a webapp using perl
and cgi with postgresql 7.4.6 as a backend database. One of the
things I need is to create a transactions table that will record a row
of data and then return the transaction id to the caller. I have
decided to implement this piece of the webapp as a stored procedure in
pl/pgsql.
The following is the source code...
Firstly, I think you should look up the documentation for nextval(),
currval(). There is no possiblity of returning the wrong value even if
another transaction is running concurrently. And you don't need to lock
the table, which is good for performance.
<snip>
# select
trx_id(JUANCASERO3055128218,CREDIT,02/02/05,1,1,Aventura,02/01/05,Tom)
;I get the following error message
ERROR: column "juancasero3055128218" does not exist
You need to put those values into strings. Unless you do that it thinks
you're referring to a column named that. Try:
select
trx_id('JUANCASERO3055128218','CREDIT','02/02/05',1,1,'Aventura','02/01/
05','Tom');
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
a tool for doing 5% of the work and then sitting around waiting for
someone else to do the other 95% so you can sue them.
Import Notes
Resolved by subject fallback
On Fri, Feb 04, 2005 at 12:22:43PM -0600, Juan Casero (FL FLC) wrote:
I tried putting those values into strings like you describe below but
then the server bombs. e.g...customer_service=# select
trx_id('JUANCASERO3055128218','CREDIT','02/02/05','1','1','Aventura','02
/01/05','Tom');
ERROR: function trx_id("unknown", "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
customer_service=#
Try checking your function definition. I'm counting nine parameters but
you only gave eight...
Also, your example with type casts, you *still* need to use quotes. You
*always* need to quote strings.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
On Fri, Feb 04, 2005 at 12:44:35PM -0600, Juan Casero (FL FLC) wrote:
Sorry about that. I did forget one parameter...
customer_service=# select
trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
,'02/01/05','Tom');
ERROR: function trx_id("unknown", integer, "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
Please post the output of: \df+ trx_id
That will tell you what parameters it expects.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Import Notes
Reply to msg id not found: 7583B3F1891CC0429FA4A44952AA539C0BD595@wfm-exchprd2.wfm.pvtReference msg id not found: 7583B3F1891CC0429FA4A44952AA539C0BD595@wfm-exchprd2.wfm.pvt | Resolved by subject fallback
Here is the output of that command. I ran it in a unix shell and
redirected the psql output to a file so I haven't touched it...
Result data type | Schema | Name |
Argument data types | Owner |
Language |
Source code
| Description
------------------+--------+--------+-----------------------------------
-------------------------------------------------------------+----------
+----------+------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------------+-------------
integer | public | trx_id | character varying, smallint,
character, date, character, character, character, date, character |
postgres | plpgsql | DECLARE
p_customer_id ALIAS FOR $1;
p_store ALIAS FOR $2;
p_trx_type ALIAS FOR $3;
p_trx_date ALIAS FOR $4;
p_reissue ALIAS FOR $5;
p_receipt ALIAS FOR $6;
p_purchloc ALIAS FOR $7;
p_purchdate ALIAS FOR $8;
p_associate ALIAS FOR $9;
p_trx_id transactions.trx_id%TYPE;
BEGIN
INSERT INTO transactions VALUES
(nextval('trx_id_seq'),'p_customer_id',p_store,'p_trx_type','p_trx_date'
,'p_reissue','p_receipt','p_purch_loc','p_purch_date','p_associate');
SELECT INTO p_trx_id currentval('trx_id_seq');
RETURN (p_trx_id);
END;
|
(1 row)
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Friday, February 04, 2005 1:56 PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 12:44:35PM -0600, Juan Casero (FL FLC) wrote:
Sorry about that. I did forget one parameter...
customer_service=# select
trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
,'02/01/05','Tom');
ERROR: function trx_id("unknown", integer, "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
Please post the output of: \df+ trx_id
That will tell you what parameters it expects.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
a tool for doing 5% of the work and then sitting around waiting for
someone else to do the other 95% so you can sue them.
Import Notes
Resolved by subject fallback
By the way. I took your advice and redesigned the tables and the
function so that it is not needed to lock the table at all. I assume
this work because of MVCC.
Thanks,
juan
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Friday, February 04, 2005 1:56 PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 12:44:35PM -0600, Juan Casero (FL FLC) wrote:
Sorry about that. I did forget one parameter...
customer_service=# select
trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
,'02/01/05','Tom');
ERROR: function trx_id("unknown", integer, "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
Please post the output of: \df+ trx_id
That will tell you what parameters it expects.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
a tool for doing 5% of the work and then sitting around waiting for
someone else to do the other 95% so you can sue them.
Import Notes
Resolved by subject fallback
On Fri, Feb 04, 2005 at 01:14:44PM -0600, Juan Casero (FL FLC) wrote:
Here is the output of that command. I ran it in a unix shell and
redirected the psql output to a file so I haven't touched it...
Well, here's the problem. Your definition is:
integer | public | trx_id | character varying, smallint,
character, date, character, character, character, date, character |
postgres | plpgsql |
And your call is:
customer_service=# select
trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
,'02/01/05','Tom');
ERROR: function trx_id("unknown", integer, "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
You didn't quote the second argument so PostgreSQL assumes it's an
integer whereas your definition asks for a smallint. So you either need
to quote the number like '805', or cast it like 805::smallint.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Martijn -
Thank you so much for your help. I finally got the stored procedure to
work as I wanted and your advice on nextval() and currval() helped me
get around the expected problem of how to address two transactions
trying to acquire a lock on the same table.
Best Regards,
Juan
-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Friday, February 04, 2005 4:49 PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 01:14:44PM -0600, Juan Casero (FL FLC) wrote:
Here is the output of that command. I ran it in a unix shell and
redirected the psql output to a file so I haven't touched it...
Well, here's the problem. Your definition is:
integer | public | trx_id | character varying, smallint,
character, date, character, character, character, date, character |
postgres | plpgsql |
And your call is:
customer_service=# select
trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
,'02/01/05','Tom');
ERROR: function trx_id("unknown", integer, "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not
exist
HINT: No function matches the given name and argument types. You
may
need to add explicit type casts.
You didn't quote the second argument so PostgreSQL assumes it's an
integer whereas your definition asks for a smallint. So you either need
to quote the number like '805', or cast it like 805::smallint.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
a
tool for doing 5% of the work and then sitting around waiting for
someone
Show quoted text
else to do the other 95% so you can sue them.
Import Notes
Resolved by subject fallback