create function

General Discussion
1

Hi,

I’ve got a Problem with creating a function:

     <![CDATA[          CREATE FUNCTION

               heimtipps( tsp_id int4 )

             RETURNS int4

             AS’
             DECLARE

                anzahl int4;
             BEGIN
           SELECT
             count(*)
           INTO
             anzahl
           FROM
             spieltipp
           WHERE
             tippspiel_id = tsp_id
           AND
             spieltipp_heimtipp > spieltipp_gasttipp;    
               RETURN

                 anzahl;
             END’

             LANGUAGE ‘plpgsql’;
        ]]>

When I create the function direct to the postgres-database via console, it’s created correctly

doing this via liquibase, I got the following error:

    Migration Failed: Error executing SQL CREATE FUNCTION

               heimtipps( tsp_id int4 )

             RETURNS int4

             AS’
             DECLARE

                anzahl int4.  For more information, use the --logLevel flag)
    24.06.2009 16:58:22 liquibase.commandline.Main main
    SCHWERWIEGEND: Error executing SQL CREATE FUNCTION

               heimtipps( tsp_id int4 )

             RETURNS int4

             AS’
             DECLARE

                anzahl int4
    liquibase.exception.MigrationFailedException: Migration failed for change set de/kicktipp/liquibase/changelogs/changelog-0139.xml::1::janning:
        Reason: liquibase.exception.JDBCException: Error executing SQL CREATE FUNCTION

               heimtipps( tsp_id int4 )

             RETURNS int4

             AS’
             DECLARE

                anzahl int4:
             Caused By: Error executing SQL CREATE FUNCTION

               heimtipps( tsp_id int4 )

             RETURNS int4

             AS’
             DECLARE

                anzahl int4:
             Caused By: ERROR: unterminated quoted string at or near "’
             DECLARE

                anzahl int4"
    at liquibase.ChangeSet.execute(ChangeSet.java:238)
    at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
    at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
    at liquibase.Liquibase.update(Liquibase.java:112)
    at liquibase.commandline.Main.doMigration(Main.java:646)
    at liquibase.commandline.Main.main(Main.java:95)

Seems that liquibase expect a " ’ " when it gets to the first " ; "!? But, of course the terminating " ’ " is at the end of the function.

Can anybody help me? Thanks for all answers.

kind regards
peter

2

Peter,

try add attributes to sql tag

Cheers, Oleg

3

Thank you Oleg, that works.

4

Even easier would be (if I have read correctly) to use instead of .