PgSqlScript and CREATE FUNCTION
Posted: Wed 25 Aug 2010 10:13
Hi,
I'm trying to use PgSqlScript to execute a database script which updates the schema of the database automatically, so the user doesn't have to.
That script is generated elsewhere. I have a file, which usually psql eats, outputting some errors which are to be ignored. Now, I have that file as an embedded resource in my executable and wan't to execute.
I tried doing it with PgSqlDump and PgSqlScript, PgSqlScript seems to be the one intended for that purpose. However, I'm running into a lot of troubles. I found out that I had to remove comments and rework the whitespace (mainly remove newlines). That's ok.
But I keep having this error: "cannot insert multiple commands into a prepared statement". Well, I suppose that's why I'm using PgSqlScript. Well, I figure PgSqlScript is having trouble separating the statements due to semicolons inside the text of plpgsql functions. I just can't figure how to do it.
The doc says "When PgSqlScript encounters a BEGIN keyword in the script, it looks for a slash symbol "/" in the first position of some consecutive line. Everything between the BEGIN and the slash is considered single block. Usually the slash is placed after the line containing END keyword. You do not have to place slash after nested BEGIN...END block." But this is a bit obscure... Are those BEGIN and END and slashes removed before sending the command to PostgreSQL ? Certainly I can't put the slashes near the BEGIN and END of my functions, because the DECLARE keyword appears before BEGIN, and semicolons can appear before BEGIN...
Suppose I have this function:
Where would I insert the BEGINs, ENDs and slashes?
Thanks,
I'm trying to use PgSqlScript to execute a database script which updates the schema of the database automatically, so the user doesn't have to.
That script is generated elsewhere. I have a file, which usually psql eats, outputting some errors which are to be ignored. Now, I have that file as an embedded resource in my executable and wan't to execute.
I tried doing it with PgSqlDump and PgSqlScript, PgSqlScript seems to be the one intended for that purpose. However, I'm running into a lot of troubles. I found out that I had to remove comments and rework the whitespace (mainly remove newlines). That's ok.
But I keep having this error: "cannot insert multiple commands into a prepared statement". Well, I suppose that's why I'm using PgSqlScript. Well, I figure PgSqlScript is having trouble separating the statements due to semicolons inside the text of plpgsql functions. I just can't figure how to do it.
The doc says "When PgSqlScript encounters a BEGIN keyword in the script, it looks for a slash symbol "/" in the first position of some consecutive line. Everything between the BEGIN and the slash is considered single block. Usually the slash is placed after the line containing END keyword. You do not have to place slash after nested BEGIN...END block." But this is a bit obscure... Are those BEGIN and END and slashes removed before sending the command to PostgreSQL ? Certainly I can't put the slashes near the BEGIN and END of my functions, because the DECLARE keyword appears before BEGIN, and semicolons can appear before BEGIN...
Suppose I have this function:
Code: Select all
CREATE OR REPLACE FUNCTION Abc(a INT, b INT, c INT)
RETURNS INT AS $$
DECLARE a INT;
BEGIN
...
RETURN a;
END;
$$ LANGUAGE plpgsql VOLATILE;
Thanks,