PgsqlScript problem with CREATE FUNCTION

PgsqlScript problem with CREATE FUNCTION

Postby hmuscroft » Tue 26 Jun 2007 17:18

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?
hmuscroft
 
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Postby Alexey » Wed 27 Jun 2007 13:08

Just remove the semicolon after "temp_staff_id".
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL