To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		:	Frank Miles
Your email address	:	fpm@u.washington.edu


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...