Insert "$$" in text column, POSTGRESQL-CodePudding

I'm currently facing a strange issue on my postgresql query to insert string in text column, i will explain:

i have a table with following schema :

 CREATE TABLE IF NOT EXISTS template_formula
            (
                ID SERIAL PRIMARY KEY ,
                formula VARCHAR(500) DEFAULT NULL,
                display VARCHAR(500) DEFAULT NULL
);

This table will contain a formula name and a display string that will contain markdown.

My insert query is as follow :

DO $$
BEGIN 
    BEGIN
        --- Insert Template Formula 
         INSERT INTO template_formula(id,formula,display) VALUES 
         (7,'1000*(sin(deg2rad($A)))-(init($A)','$\textrm{Calcul IPI décrit comme :}$ $$R = 1000 \cdot (sin(degr2rad(A_i)))-A_0  $$ $\textrm{où :}$ $$ \textrm{$A_i$} = \textrm{Valeur courante de lecture} $$ $$ \textrm{$A_0$} = \textrm{Valeur initiale} $$')
    END;
    COMMIT;  
END;
$$

When i try to execute this query on DBeaver i get following error :

SQL Error [42601]: Unterminated dollar quote started at position 290 in SQL DO $$

This error is due to the "$$" in string inserted to display column :

$$R = 1000

have you got any idea how to escape those two characters to be considered as string ? Thx for advance.

CodePudding user response：

Use some other delimiter for the DO block:

DO $do$
BEGIN 
    BEGIN
        --- Insert Template Formula 
         INSERT INTO template_formula(id,formula,display) VALUES 
         (7,'1000*(sin(deg2rad($A)))-(init($A)','$\textrm{Calcul IPI décrit comme :}$ $$R = 1000 \cdot (sin(degr2rad(A_i)))-A_0  $$ $\textrm{où :}$ $$ \textrm{$A_i$} = \textrm{Valeur courante de lecture} $$ $$ \textrm{$A_0$} = \textrm{Valeur initiale} $$');
    END;
    COMMIT;  
END;
$do$;

Or get rid of the useless DO block completely:

BEGIN TRANSACTION;
INSERT INTO template_formula(id,formula,display) VALUES 
(7,'1000*(sin(deg2rad($A)))-(init($A)','$\textrm{Calcul IPI décrit comme :}$ $$R = 1000 \cdot (sin(degr2rad(A_i)))-A_0  $$ $\textrm{où :}$ $$ \textrm{$A_i$} = \textrm{Valeur courante de lecture} $$ $$ \textrm{$A_0$} = \textrm{Valeur initiale} $$');
COMMIT;

(You also forgot the ; to end the INSERT statement)