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
