PgSqlScript throwing "cannot insert multiple commands into a prepared statement"

[hmuscroft]

Hi,

I am trying to execute the following script using the PgSqlScript component :-

Code: Select all

CREATE OR REPLACE FUNCTION grant_std_user (IN  text) 	
RETURNS bool AS
$BODY$
	begin
	EXECUTE 'GRANT ALL ON ' || $1 || ' to std_user';
	return TRUE;
	end;
$BODY$
	LANGUAGE PLpgSQL
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;

CREATE OR REPLACE FUNCTION before_post_record () 	
RETURNS trigger AS
$BODY$
	DECLARE
	staff_id int4;
	BEGIN
	staff_id := current_staff_id();
	
	if (TG_OP = 'INSERT') THEN
	if (NEW.id IS NULL) THEN
	NEW.ID := generate_id();
	END IF;
	NEW.createdon := current_timestamp;
	NEW.createdby_staff_id := staff_id;
	END IF;
	
	NEW.updatedon := current_timestamp;
	NEW.updatedby_staff_id := staff_id;
	
	RETURN NEW;
	END;
$BODY$
	LANGUAGE PLpgSQL
	CALLED ON NULL INPUT
	IMMUTABLE
	EXTERNAL SECURITY INVOKER;

The error being thrown is :-

CoreLab.PostgreSql.PgSqlException was unhandled by user code
Message="cannot insert multiple commands into a prepared statement"
Source="CoreLab.PostgreSql"
ErrorCode=-2147467259
CallStack=""
DetailMessage=""
FileName="postgres.c"
Hint=""
LineNumber=1125
Position=0
ProcedureName="exec_parse_message"
StackTrace:
at CoreLab.Common.DbScript.ExecuteNext(IDataReader& reader)
at CoreLab.Common.DbScript.Execute()
at co2005_to_2007_pgsql.frmMain.btStart_Click(Object sender, EventArgs e) in E:\vs2005\source\co2005_to_2007_pgsql\co2005_to_2007_pgsql\frmMain.cs:line 417
at System.Windows.Forms.Control.OnClick(EventArgs e)
.....

Can you help please?

Kind Regards,

Hedley Muscroft

[Alexey]

You should add "/" symbol before second CREATE statement.

[hmuscroft]

Hi Alexey - thanks for the reply - it works now. I am a little confused though. If I execute a series of simple statements such as :-

Code: Select all

update table1 set field='something' where id=2;
update table2 set field='something' where id=3;

That all works fine without the need of inserting '/'s inbetween statements. When do I use '/'s and when is it not necessary? Also - is this standard PostgreSQL notation?

Thanks.

[Alexey]

You should use '/'s when you need to separate two blocks, for example two CREATE statements.

[hmuscroft]

Ok - thanks for your help Alexey.

[Alexey]

Not at all.