3

I am using Liquibase to create functions and getting error when trying to use < operator in SQL.

SQL:

     <createProcedure>
        CREATE OR REPLACE FUNCTION function(dateFrom timestamp, dateTo timestamp ) 
        RETURNS TABLE
        LANGUAGE plpgsql
        AS $$
        BEGIN
        SELECT * FROM
        ORDER BY date DESC
        WHERE date >= $2 AND date <  $3
        RETURN QUERY;
        END;
        $$;
     </createProcedure>

Error:

The content of elements must consist of well-formed character data or markup.

In WHERE date >= $2 AND date < $3

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
JDivani
  • 37
  • 5

1 Answers1

6

XML does not allow to use < or > inside the value of a tag, unless you wrap the content of that tag into a CDATA section:

<createProcedure>
 <![CDATA[ 
    CREATE OR REPLACE FUNCTION function(dateFrom timestamp, dateTo timestamp ) 
    RETURNS TABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
    SELECT * FROM
    ORDER BY date DESC
    WHERE date >= $2 AND date <  $3
    RETURN QUERY;
    END;
    $$;
  ]]> 
</createProcedure>

Unrelated to the Liquibase question: functions wrapping simple SQL queries are better defined as SQL functions to avoid the PL/pgSQL overhead:

<createProcedure>
 <![CDATA[ 
    CREATE OR REPLACE FUNCTION function(dateFrom timestamp, dateTo timestamp ) 
    RETURNS TABLE (...)
    LANGUAGE sql
    AS $$
      SELECT * 
      FROM ...
      WHERE date >= $2 
        AND date <  $3
      ORDER BY date DESC
    $$;
  ]]> 
</createProcedure>