From: | Erik Dahlstrand <df03daer(at)ing(dot)hj(dot)se> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Trigger won't execute when using sp with input parameters |
Date: | 2005-04-09 13:23:54 |
Message-ID: | 4257D76A.3020107@ing.hj.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)123(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-04-09 15:59:38 | Re: Trigger won't execute when using sp with input parameters |
Previous Message | CiaMba | 2005-04-09 11:32:22 | 2 questions: Installation And DB runtime creation |