Thread: 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,transactions.trx_typeTYPE,transactions.trx_date%TYPE,transactions.reissue%TYPE,transactions.receipt TYPE,transactions.purch_loc%TYPE,transactions.purch_date%TYPE,transactions.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_date,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/
> 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.
Attachment
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/
> 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: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/
> 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.
Attachment
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/
> 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.
Attachment
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/
> 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.
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/
> 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 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
> else to do the other 95% so you can sue them.
Attachment
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
> else to do the other 95% so you can sue them.