PgSqlScript and CREATE FUNCTION

[migle]

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:

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;

Where would I insert the BEGINs, ENDs and slashes?

Thanks,

[Shalex]

This issue was discussed in this thread. As a temporary workaround, please try setting the 'Unprepared Execute' connection string parameter to true.

[migle]

I worked around it by reading the whole script into a string, and doing some search replace on that string using regular expressions, adding a row containing a single slash after all BEGIN statements, all CREATE FUNCTIONs and all CREATE TRIGGERs.

Ugly, but it will work as long as the script is under my control.

The "Unprepared execute" string would cause me more trouble, as I mean to have a single place for creating a database connection on the whole application.

[Shalex]

I will post here when the build with the fix is available for download.

[Shalex]

New build of dotConnect for PostgreSQL 4.95.170 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=19070 .