Code: Select all
/CREATE OR REPLACE FUNCTION setup_table_triggers (IN text, IN text)
RETURNS bool AS
$BODY$
BEGIN
EXECUTE 'CREATE TRIGGER trig_' || $2 || '_before_post
BEFORE INSERT OR UPDATE
ON ' || $1 || '.' || $2 || ' FOR EACH ROW
EXECUTE PROCEDURE before_post_record();';
EXECUTE 'CREATE TRIGGER trig_' || $2 || '_after_delete
AFTER DELETE
ON ' || $1 || '.' || $2 || ' FOR EACH ROW
EXECUTE PROCEDURE after_delete_record();';
RETURN TRUE;
END;
$BODY$
LANGUAGE PLpgSQL
CALLED ON NULL INPUT
VOLATILE
EXTERNAL SECURITY DEFINER;
/CREATE OR REPLACE FUNCTION current_staff_id ()
RETURNS int8 AS
$BODY$
SELECT staff_id FROM temp_staff_id;
$BODY$
LANGUAGE SQL
CALLED ON NULL INPUT
STABLE
EXTERNAL SECURITY INVOKER;
In other words, it's failing on the current_staff_id() due to some kind of delimitation problem.CoreLab.PostgreSql.PgSqlException was unhandled
Message="unterminated dollar-quoted string at or near \"$BODY$\r\n\tSELECT staff_id FROM temp_staff_id\""
Source="CoreLab.Data"
ErrorCode=-2147467259
CallStack=""
DetailMessage=""
FileName="scan.l"
Hint=""
LineNumber=795
Position=68
ProcedureName="base_yyerror"
StackTrace:
at CoreLab.Common.DbScript.ExecuteNext(IDataReader& reader)
at CoreLab.Common.DbScript.Execute()
at co2007_db_util.frmMain.btPGSQL2_Click(Object sender, EventArgs e) in E:\vs2005\source\co2007_db_util\co2007_db_util\frmMain.cs:line 236
at System.Windows.Forms.Control.OnClick(EventArgs e)...
I have several other PLPGSQL functions in my database and the PgsqlScript component handles them perfectly. The only difference with the current_staff_id() function is that it is a SQL function rather than a PLPGSQL function.
If I change "current_staff_id()" function to a PLPGSQL function as follows :-
Code: Select all
/CREATE OR REPLACE FUNCTION current_staff_id ()
RETURNS int8 AS
$BODY$
BEGIN
RETURN SELECT staff_id FROM temp_staff_id;
END;
$BODY$
LANGUAGE PLpgSQL
CALLED ON NULL INPUT
STABLE
EXTERNAL SECURITY INVOKER;
Any suggestions please?