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
