Syntax error when using queryInterface.createFunction()

[uhuaha]

What are you doing?

I am writing a queryInterface.createFunction() in a migration file that looks like this:

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
   await queryInterface.createFunction(
      "building.getBuildingIds",
      [
        {type: "float", name: "longitude", direction: "IN"},
        {type: "float", name: "latitude", direction: "IN"}
      ],
      "TABLE(geometry geometry, building_id uuid, distance double precision)",
      "sql",
      `select
            intersection.geometry,
            intersection.building_id,
            ST_Distance(intersection.geometry, ST_MakePoint(longitude, latitude)::geography) as distance
        from (
            select
                building_id,
                geometry
            from building.building
            where ST_Intersects (
                geometry,
                ST_Buffer(ST_MakePoint(longitude, latitude)::geography, 1)::geometry
            )
        ) as intersection
      order by distance asc;`
    );
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropFunction("building.getBuildingIds");
  }
};

When I run the migration I get the following error message:

Executing (default): CREATE FUNCTION building.getBuildingIds(IN longitude float, IN latitude float) RETURNS TABLE(geometry geometry, building_id uuid, distance double precision) AS $func$  BEGIN select
            intersection.geometry,
            intersection.building_id,
            ST_Distance(intersection.geometry, ST_MakePoint(longitude, latitude)::geography) as distance
        from (
            select
                building_id,
                geometry
            from building.building
            where ST_Intersects (
                geometry,
                ST_Buffer(ST_MakePoint(longitude, latitude)::geography, 1)::geometry
            )
        ) as intersection
      order by distance asc; END; $func$ language 'sql';
 MIGRATE ERROR { SequelizeDatabaseError: Syntaxfehler bei »select«
    at Query.formatError (C:\development\infrastructure-building_meinFeature\node_modules\sequelize\lib\dialects\postgres\query.js:354:16)
    at query.catch.err (C:\development\infrastructure-building_meinFeature\node_modules\sequelize\lib\dialects\postgres\query.js:71:18)
    at tryCatcher (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\util.js:16:23)
    at Promise._settlePromiseFromHandler (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\promise.js:517:31)
    at Promise._settlePromise (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\promise.js:574:18)
    at Promise._settlePromise0 (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\promise.js:619:10)
    at Promise._settlePromises (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\promise.js:695:18)
    at _drainQueueStep (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\async.js:138:12)
    at _drainQueue (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\async.js:131:9)
    at Async._drainQueues (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\async.js:147:5)
    at Immediate.Async.drainQueues [as _onImmediate] (C:\development\infrastructure-building_meinFeature\node_modules\bluebird\js\release\async.js:17:14)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
  name: 'SequelizeDatabaseError',
  parent:
   { error: Syntaxfehler bei »select«
       at Connection.parseE (C:\development\infrastructure-building_meinFeature\node_modules\pg\lib\connection.js:602:11)
       at Connection.parseMessage (C:\development\infrastructure-building_meinFeature\node_modules\pg\lib\connection.js:399:19)
       at Socket.<anonymous> (C:\development\infrastructure-building_meinFeature\node_modules\pg\lib\connection.js:121:22)
       at Socket.emit (events.js:189:13)
       at addChunk (_stream_readable.js:284:12)
       at readableAddChunk (_stream_readable.js:265:11)
       at Socket.Readable.push (_stream_readable.js:220:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     name: 'error',
     length: 85,
     severity: 'FEHLER',
     code: '42601',
     detail: undefined,
     hint: undefined,
     position: '175',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'scan.l',
     line: '1124',
     routine: 'scanner_yyerror',
     sql: ...

What do you expect to happen?

I expect the same CREATE FUNCTION statement as above but without the BEGIN and END since I use language=sql (if I specify language=plpgsql then the described error doesn't happen).

Environment

PostgreSQL, Sequelize version: 5.13.1

[papb]

Hello!

I expect the same CREATE FUNCTION statement as above but without the BEGIN and END

Can you please post exactly what SQL you wanted to see instead?

[uhuaha]

What I would like to see happen instead is: If I select "language=sql" the function gets created without problem and if I check it in pgAdmin I will see:

select
       intersection.geometry,
       intersection.building_id,
       ST_Distance(intersection.geometry, ST_MakePoint(longitude, latitude)::geography) as distance
from (
       select
                building_id,
                geometry
       from building.building
       where ST_Intersects (
                geometry,
                ST_Buffer(ST_MakePoint(longitude, latitude)::geography, 1)::geometry
       )
) as intersection
order by distance asc;

Note: there is no BEGIN and END keywords in the function - unlike when I set "language=plpgsql".

I suspect that the error message points to the first select where there is a BEGIN that is not expected because of language=sql.

[papb]

Please tell me if I understood correctly.

What was generated by Sequelize

CREATE FUNCTION building.getBuildingIds(IN longitude float, IN latitude float) RETURNS TABLE(geometry geometry, building_id uuid, distance double precision) AS $func$  BEGIN select
    intersection.geometry,
    intersection.building_id,
    ST_Distance(intersection.geometry, ST_MakePoint(longitude, latitude)::geography) as distance
from (
    select
        building_id,
        geometry
    from building.building
    where ST_Intersects (
        geometry,
        ST_Buffer(ST_MakePoint(longitude, latitude)::geography, 1)::geometry
    )
) as intersection
order by distance asc; END; $func$ language 'sql';

What should have been generated instead

CREATE FUNCTION building.getBuildingIds(IN longitude float, IN latitude float) RETURNS TABLE(geometry geometry, building_id uuid, distance double precision) AS $func$  select
    intersection.geometry,
    intersection.building_id,
    ST_Distance(intersection.geometry, ST_MakePoint(longitude, latitude)::geography) as distance
from (
    select
        building_id,
        geometry
    from building.building
    where ST_Intersects (
        geometry,
        ST_Buffer(ST_MakePoint(longitude, latitude)::geography, 1)::geometry
    )
) as intersection
order by distance asc; $func$ language 'sql';

---

Is this correct? (I simply deleted BEGIN and END; from the query)

[uhuaha]

Yes, Pedro, you understood correctly. The latter is what I would like to have generated. If there are no more BEGIN and END, then, the syntax error should be gone. It would be great to have that fixed in a new version as soon as possible. Thank you very much!

[flashspys]

I could make the merge request. Would be the solution to remove the insertion of BEGIN and END and to leave this to the function author (what would be the best solution imho), or to make this dependent from the language used in the query? Or to make a new option?

[papb]

I could make the merge request.

Hello @flashspys, that is great!

Would be the solution to remove the insertion of BEGIN and END and to leave this to the function author (what would be the best solution imho), or to make this dependent from the language used in the query? Or to make a new option?

If I understood correctly, this problem does not happen in other language choices, therefore I think this is a bug of the "sql" language usage. I think the correct fix for this is to check if the language is "sql" and if so, do not insert BEGIN and END in the resulting query.

[uhuaha]

This sounds very good to me. Thank you guys!

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂