Обсуждение: Trigger won't execute when using sp with input parameters

Hi!

I'm not sure if this has anything to do with the npgsql data provider.
I'm trying to implement tsearch2 functionality within a table.
Each time a row is inserted or updated the following trigger should
execute:

CREATE TRIGGER tsvectorupdate
 BEFORE INSERT OR UPDATE
 ON "public".object
 FOR EACH ROW
 EXECUTE PROCEDURE tsearch2(idxfti, header, description);

Insertions are made from an ASP.NET page using npgsql.

When using an ''INSER INTO' SQL statement the trigger is executing fine.
Also a stored procedure with "hard coded" values will fire the trigger.

myCommand.CommandText = "SELECT
insert_object('book',61,'ABC','DEF',110,'S','abc@123.com')";

But when using input parameters (and a output parameter) the trigger
won't execute.

myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "SELECT
insert_object(:db_table_destination,:category_id,:header,:description,:price,:type_of_ad,:person_id)";


Below is the stored procedure:

CREATE OR REPLACE FUNCTION insert_object("varchar", int4, "varchar",
"varchar", "numeric", "varchar", "varchar")
 RETURNS int4 AS
$BODY$DECLARE
   insertString text;
   id int4;

BEGIN
   insertString := 'INSERT INTO ' || _db_table_destination
                || ' (category_id, header, description, price,
created_on, type_of_ad, person_id) '
                || 'VALUES (' || quote_literal(_category_id) || ','
                || quote_literal(_header) || ','
                || quote_literal(_description) || ','
                || quote_literal(_price) || ','
                || 'DEFAULT' || ','
                || quote_literal(_type_of_ad) || ','
                || quote_literal(_person_id) || ');';

   --RAISE NOTICE '%', insertString;
   EXECUTE insertString;

   id := currval('object_id_seq');

   RETURN id;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_object("varchar", int4, "varchar", "varchar",
"numeric", "varchar", "varchar") OWNER TO postgres;


Any ideas?
Kind regards, Erik

Erik Dahlstrand <df03daer@ing.hj.se> writes:
> When using an ''INSER INTO' SQL statement the trigger is executing fine.
> Also a stored procedure with "hard coded" values will fire the trigger.
> But when using input parameters (and a output parameter) the trigger
> won't execute.

That's really, really hard to believe --- and if it were true it would
be a critical bug.  Can you provide a complete, self-contained example
that makes this happen?  I can't reproduce it with what you gave, since
the insert_object() function doesn't work at all --- it looks like you
stripped out chunks of code.  A SQL script that creates the tables,
the function, and causes the failure from a standing start is the best
way to demonstrate such problems.

            regards, tom lane