PgSqlScript and CREATE FUNCTION

PgSqlScript and CREATE FUNCTION

Postby migle » 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:

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,
migle
 
Posts: 27
Joined: Tue 19 Jan 2010 13:52

Postby Shalex » Thu 26 Aug 2010 10:48

This issue was discussed in this thread. As a temporary workaround, please try setting the 'Unprepared Execute' connection string parameter to true.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby migle » Mon 30 Aug 2010 14:37

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.
migle
 
Posts: 27
Joined: Tue 19 Jan 2010 13:52

Postby Shalex » Tue 31 Aug 2010 15:59

I will post here when the build with the fix is available for download.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Fri 24 Sep 2010 14:33

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 .
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL