Trigger won't execute when using sp with input parameters

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

Responses

Browse pgsql-novice by date

  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