SQLA 16.20 No Syntax Highlighting with dollar-quoted escaping

Database
Enthusiast

SQLA 16.20 No Syntax Highlighting with dollar-quoted escaping

I'm trying to compile an SP (function) with dollar-quote escaping:

 

 

CREATE OR REPLACE FUNCTION dcpViews.SP_DCPUpsertUser (...) 
RETURNS INTEGER AS
$$
DECLARE NewUserId INTEGER; -- Track how many new users were created
BEGIN   

    -- Get new userid
    SELECT NextValue INTO NewUserId
    FROM dcp.NextId
    WHERE IdType = 'User';

    RETURN NewUserId;   
END;
$$
LANGUAGE 'plpgsql';


The SP compiles fine in SQLA, but the SP body within the $$ bookends have no syntax highlighting and are treated as if they are literal text inside single quotes. They're pink!

 

Capture.JPG

I tried using a different escape name, like $BODY$, but then SQLA complains about "unterminated dollar-quoted string at or near "$BODY$".  It compiles fine outside of SQLA.

 

Is there a setting in SQL so I can preserve the syntax highlighing here? Or is there some ODBC setting? Thanks.

 

SQLA 16.20.0.1

Postgres ODBC Driver 10.01.0000


Accepted Solutions
Teradata Employee

Re: SQLA 16.20 No Syntax Highlighting with dollar-quoted escaping

According to the PostgreSQL SQL manual $$ is one of the string delimiters it supports, so anything between $$ delimiters will be shown as a string.

The other delimiters are single quotes with a couple of optional prefixes:

‘**bleep**’, e‘**bleep**’, b‘**bleep**’, $$**bleep**$$

will all be treated as strings.

 

Note that if you want to be able to include the $$ symbols and NOT have it treat the content as a string [for syntax highlighting purposes] you can simply remove the following lines from near the end of the postresql.lng file:

 

            <Scope BracketHighlight="True">

                        <ExplicitPatternGroup TokenID="33" Type="StartScope" TokenKey="StartString" PatternValue="$$" />

                        <ExplicitPatternGroup Type="EndScope" TokenKey="EndString" PatternValue="$$" />    

            </Scope>

 

The file is in the data sub directory of wherever you installed SQL Assistant.

Usually c:\Program Files (x86)\Teradata\Client\16.20\Bin\Data.

 

The language definition files, member list files, and syntax example files can all be modified by the customer/user if they wish. The language file requires a bit more knowledge and care but in most cases the kind of changes you may want to do are very simple. There is a section in the user guide describing all the control files – although not much detail in the case of the language files.

 

Mike

1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

Re: SQLA 16.20 No Syntax Highlighting with dollar-quoted escaping

Quick update...if I change the $$ to $BODY$, and I Execute Parallel (F9) instead of Execute (F5), it works.

Teradata Employee

Re: SQLA 16.20 No Syntax Highlighting with dollar-quoted escaping

According to the PostgreSQL SQL manual $$ is one of the string delimiters it supports, so anything between $$ delimiters will be shown as a string.

The other delimiters are single quotes with a couple of optional prefixes:

‘**bleep**’, e‘**bleep**’, b‘**bleep**’, $$**bleep**$$

will all be treated as strings.

 

Note that if you want to be able to include the $$ symbols and NOT have it treat the content as a string [for syntax highlighting purposes] you can simply remove the following lines from near the end of the postresql.lng file:

 

            <Scope BracketHighlight="True">

                        <ExplicitPatternGroup TokenID="33" Type="StartScope" TokenKey="StartString" PatternValue="$$" />

                        <ExplicitPatternGroup Type="EndScope" TokenKey="EndString" PatternValue="$$" />    

            </Scope>

 

The file is in the data sub directory of wherever you installed SQL Assistant.

Usually c:\Program Files (x86)\Teradata\Client\16.20\Bin\Data.

 

The language definition files, member list files, and syntax example files can all be modified by the customer/user if they wish. The language file requires a bit more knowledge and care but in most cases the kind of changes you may want to do are very simple. There is a section in the user guide describing all the control files – although not much detail in the case of the language files.

 

Mike

Enthusiast

Re: SQLA 16.20 No Syntax Highlighting with dollar-quoted escaping

Genius, that did the trick.  Thank you!