PgsqlScript problem with CREATE FUNCTION

[hmuscroft]

Here is a portion of the SQL script I am attempting to run :-

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;

When running this script using a PgsqlScript component, the exception is :-

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)...

In other words, it's failing on the current_staff_id() due to some kind of delimitation problem.

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;

... then the script runs ok. Unfortunately, I need it to handle SQL functions in the script too.

Any suggestions please?

[Alexey]

Just remove the semicolon after "temp_staff_id".