see attached bug-report
To report any other bug, fill out the form below and e-mail it to
[EMAIL PROTECTED]
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Frank Miles
Your email address : [EMAIL PROTECTED]
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.17 ELF
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0) : 2.95.2
Please enter a FULL description of your problem:
------------------------------------------------
If an index is created based on a function of the primary key,
you cannot insert new entries into the database.
(this occurs for pl/pgsl , anyway)
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
/* fun_test.sql Test table-function insert problem
*/
BEGIN;
CREATE TABLE test_table (
tt_id serial PRIMARY KEY,
tt_descr text NOT NULL
);
INSERT INTO test_table (tt_descr) VALUES ('first test record');
SELECT * FROM test_table;
CREATE FUNCTION tt_dep(int) RETURNS text AS '
DECLARE
dum_int ALIAS FOR $1;
prec record;
ttmp text;
j2 int;
BEGIN
SELECT INTO prec * FROM test_table WHERE tt_id = dum_int;
IF NOT FOUND THEN
RAISE EXCEPTION ''project % not found'', dum_int;
END IF;
ttmp := CAST(prec.tt_id AS TEXT);
j2= char_length(ttmp);
RETURN rpad(''Prefix'',j2,ttmp);
END;
' LANGUAGE 'plpgsql';
-- demonstrate simple function
SELECT tt_dep(1);
-- insert still works
INSERT INTO test_table (tt_descr) VALUES ('second test record');
SELECT * FROM test_table;
-- add problematic index
CREATE INDEX demo_index ON test_table ( tt_dep(tt_id) );
-- function still works
SELECT tt_dep(2);
-- insert will die
INSERT INTO test_table (tt_descr) VALUES ('third - will fail');
COMMIT;
/* end of fun_test.sql
*/
Clearly the above function is a contrived example. In the original problem
I had a function that was far more complex, depending on other columns
in the table as well as the primary key.
My humble apologies if I've missed documentation relating to this apparent
deficiency.
Hope this is an easy one!
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
sorry...
