This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
PostgreSQL uses specific syntax to embed multiple SQL statements within single SQL statement to create stored procedures like following (note the '$$' tag): - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - CREATE OR REPLACE FUNCTION dummy(IN dummy_arg varchar) RETURNS varchar LANGUAGE plpgsql AS $$ DECLARE dummy_result varchar; BEGIN select concat('dummy(', dummy_arg, ')') into dummy_result; return dummy_result; END $$; select dummy('my_dummy_arg') - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Unfortunately executing that script in NetBeans fails with error ... Error code 0, SQL state 42601: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE dummy_result varchar" Position: 94 Line 2, column 1 ... this happens because NetBeans' SQL runner ignores the '$$' quotation mark and splits statement at nearest ';', which is "dummy_result varchar;" in the middle of SQL statement. It would be nice to make NetBeans support PostrgreSQL. Dollar-quoted strings are described here: http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING As far as I understand, implementation should be pretty simple - if SQL parser finds a word starting with '$' and ending with '$' (without whitespace inside) - this word should be treated as '"' or '"' for regular strings, i.e. all other characters until next occurrence of the '$tag$' should be NOT recognized as any kind of SQL-statement splitter, string terminator and so on. Note: another $$-quoted string can be embedded inside.
Sometimes it looks a no SQL vendor took time to look right and left before implementing its features. I'm very reluctant, as this is not a quoted string - it is basicly a change of delimiter. You can use this: ================================================================= -- DELIMITER ;;; CREATE OR REPLACE FUNCTION dummy(IN dummy_arg varchar) RETURNS varchar LANGUAGE plpgsql AS $$ DECLARE dummy_result varchar; BEGIN select concat('dummy(', dummy_arg, ')') into dummy_result; return dummy_result; END $$; ;;; -- DELIMITER ; select dummy('my_dummy_arg') ================================================================= This changes the netbeans separator to ";;;" (choose something sensible for you). This is a workaround, but it should work.
Yes, in my particular case this is closer to 'local change of statement delimiter' but in general for PostgreSQL - its is really quoted string. Here is sample from their docs (regular SQL string quote marker ' is replaced with $$): ---- $$Dianne's horse$$ $SomeTag$Dianne's horse$SomeTag$ ---- Anyway using Netbeans' replace delimiter hint (which i'm aware about) is nor convenient (has to be used in 100+ files) nor really workable (in case of error, position is reported incorrectly - because server splits statements by ';' whereas Netbeans uses ';;;'). So ... since this a work stoppage for my experiments with PostgreSQL (I could not really find some other tool able to work in project-like manner with SQL files and be able to execute them against PostgreSQL BD), I've fixed that problem during weekend and created a patch (on top of default branch, rev. 296008). Please find it attached. Is there anything more from my side to make it appear in Dev build of NetBeans?
Created attachment 157986 [details] dollar-quotes-patch.v1.txt
PS: patch includes following - support for dollar-quotes strings - correct identification of the error position (implemented for PostgerSQL only), default implementation captures only position of the beginning of statement - in case of error - position of the first one is focused SQL editor
Thank you for the detailed report. The changes were split and the reporting part was fixed in the existing issue #189051. The modified statement splitter was implemented in this changeset: http://hg.netbeans.org/core-main/rev/5d95c0dc27fa In the next few days a nightly build will be produced that contains the fix. By then a message will be posted here. Please check that nightly and if you find this fixed, please change status of this issue to VERIFIED. If not please specify what did not work.
Integrated into 'main-silver', will be available in build *201601190002* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress) Changeset: http://hg.netbeans.org/main-silver/rev/5d95c0dc27fa User: Matthias Blaesing <matthias42@netbeans.org> Log: #257363: Implement Dollar-Quoted Strings (Postgresql) for statement splitter