[Database SQL Executor] plpgsql against PostgreSQL

KNIME Analytics Platform
1

Hi

I am new to KNIME and playing around with it to evaluate.

I wanted to use the Database SQL Executor to run PL/PGSQL code, see the start of it below, against a PostgreSQL database. However, it returns with following PSQLException which I suspect is cause by KNIME.

org.postgresql.util.PSQLException: Unterminated dollar quote started at position 3 in SQL do $DROP_CONS_INDS$
   declare r record. Expected terminating $$

do $DROP_CONS_INDS$
declare
   r record;
begin

I tried to escape $ by \ but to no avail. Is there a work around? Does KNIME take $DROP_CONS_INDS$ or portions of it as variable replacing it nonsensically?

Kind regards Thiemo

2

Hi Thiemo,

In KNIME you can define variables as $$DROP_CONS_IND$$. Maybe, if you define this variable as two dollar signs works?

Cheers,

Vincenzo

3

Hi Vincenzo

Thanks for the tip. It did not, however, work out. I keep getting a systax error near begin. Code was

do $$DROP$$
   begin
      raise notice 'bla';
   end
$$DROP$$

I also had a variant where I terminated end with ; but error stays the same.

Cheers Thiemo

4

Hi Thiemo,

KNIME splits up the statement in the executor by ;[NEW LINE] and executes each of the statements separately. Maybe this causes some problems with your statement. So if you want to have the complete block executed as a single statement, which I think you want, make sure that no (sub)statement ends with ;[new line]. Even putting a space after the semicolon will prevent KNIME from splitting up the statement.

Bye

Tobias