bad identation

[jostem]

hi

After DROP TABLE IF EXISTS , a bad identation is created.

Example

    SET client_min_messages TO warning;
    DROP TABLE IF EXISTS tt_LISTE_COMPTE_FACTURE;
        SET client_min_messages TO notice;

Thanks

[darold]

Hi,

I can not reproduce this issue with latest development code, which version are you using. Please give a try to your code at http://sqlformat.darold.net/ (it use current devel code) it should be fixed.

Regards,

[jostem]

Hi,

i use the online site

thanks.

[image: image.png]

Le ven. 14 déc. 2018 à 10:33, Gilles Darold notifications@github.com a écrit :

Hi,

I can not reproduce this issue with latest development code, which version are you using. Please give a try to your code at http://sqlformat.darold.net/ (it use current devel code) it should be fixed.

Regards,

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/pgFormatter/issues/81#issuecomment-447268694, or mute the thread https://github.com/notifications/unsubscribe-auth/ArvvUpaOGu19ncE6MYVvuxguxsYK4kIhks5u43BrgaJpZM4ZTIH4 .

[darold]

Ok, can you post the full content of the SQL code you want to format. The problem might come from a line before. Send it to my private email if you need privacy.

[jostem]

my sql ( i think you detect IF EXISTS in DROP like a classical IF )

CREATE OR REPLACE FUNCTION myfunc()

RETURNS void AS $§BODY§$ BEGIN set client_min_messages to warning; DROP TABLE IF EXISTS tt_BIP; DROP TABLE IF EXISTS tt_tmp; set client_min_messages to notice; END; $§BODY§$ LANGUAGE plpgsql VOLATILE COST 100;

after

CREATE OR REPLACE FUNCTION myfunc () RETURNS void AS $ BODY $BEGIN SET client_min_messages TO warning; DROP TABLE IF EXISTS tt_BIP; DROP TABLE IF EXISTS tt_tmp; SET client_min_messages TO notice; END;

$ BODY $LANGUAGE plpgsqlVOLATILECOST 100;

Le ven. 14 déc. 2018 à 14:49, Gilles Darold notifications@github.com a écrit :

Ok, can you post the full content of the SQL code you want to format. The problem might come from a line before. Send it to my private email if you need privacy.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/pgFormatter/issues/81#issuecomment-447330793, or mute the thread https://github.com/notifications/unsubscribe-auth/ArvvUuhMAZIJcRmGR4xSn6184xL6U7joks5u46x4gaJpZM4ZTIH4 .

[darold]

Commit 936865c fixes this issue. Please do not use non ANSI C characters in code separator or in object names. Here $§BODY§$ should be $BODY$, pgFormatter parser do not support them outside comments or string constants.

[jostem]

thanks for the issue.

i add § character because in script shell, i can't use $BODY$.

psql ..... <<EOF CREATE OR REPLACE FUNCTION myfunc()

RETURNS void AS $§BODY§$ BEGIN set client_min_messages to warning; DROP TABLE IF EXISTS tt_BIP; DROP TABLE IF EXISTS tt_tmp; set client_min_messages to notice; END; $§BODY§$ LANGUAGE plpgsql VOLATILE COST 100; EOF

Le ven. 14 déc. 2018 à 17:00, Gilles Darold notifications@github.com a écrit :

Commit 936865c https://github.com/darold/pgFormatter/commit/936865c22a9c9fc8819bd81383ab2785a00f1aaa fixes this issue. Please do not use non ANSI C characters in code separator or in object names. Here $§BODY§$ should be $BODY$, pgFormatter parser do not support them outside comments or string constants.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/pgFormatter/issues/81#issuecomment-447368309, or mute the thread https://github.com/notifications/unsubscribe-auth/ArvvUgl2Y54GDiPJRg_WgGxpcz2dyu_Pks5u48sQgaJpZM4ZTIH4 .

[darold]

In this case using $_BODY_$ as code separator should work in both cases.

[jostem]

thanks. i have another case for you. the original code correctly formatted is:

INSERT INTO tt_tmp_wrk1
    (
        SELECT
            MAR ,
            (
                CASE
                    WHEN MAR = 'SEUIL_DEST'
                        THEN
                        (
                            SELECT
                                COUNT(*)
                            FROM
                                CUS
                            WHERE
                                MAR.obj4 =
                                    (
                                        CASE
                                            WHEN MAR.type4 = 'CF'
                                                THEN CUS.account_no::text
                                            WHEN MAR.type4 = 'CR'
                                                THEN
CUS.owning_account_no::text
                                            WHEN MAR.type4 = 'SI'
                                                THEN CUS.point_origin
                                        END
                                    )
                                AND (
                                    CUS =
                                    (
                                        SELECT DISTINCT
                                            DD
                                        FROM
                                            PCM
                                        WHERE
                                            PCM.compo = CMF.compo
                                    )
                                    OR CUS =
                                    (
                                        SELECT DISTINCT
                                            CPIR
                                        FROM
                                            PCM
                                        WHERE
                                            PCM.compo = CMF.compo
                                    )
                                    OR CUS =
                                    (
                                        SELECT DISTINCT
                                            CPIR
                                        FROM
                                            PCM, CT, PIDR, CPIR, CMF
                                        WHERE
                                            PCM.compo = CMF.compo
                                    )
                                )
                        )
                    WHEN MAR = 'SEUIL_OBJ'
                        THEN
                        (
                            SELECT
                                COUNT(*)
                            FROM
                                CPC
                            WHERE
                                MAR.obj4 =
                                    (
                                        CASE
                                            WHEN MAR.type4 = 'CF'
                                                THEN CPC.parent::text
                                            WHEN MAR.type4 = 'CR'
                                                THEN CPC.parent::text
                                            WHEN MAR.type4 = 'SI'
                                                THEN CPC.parent::text
                                        END
                                    )
                                AND CPC IN
                                (
                                    SELECT
                                        SPT
                                    FROM
                                        SPT
                                    WHERE
                                        SPT = 'SEUIL_OBJ'
                                        AND SPT IN
                                        (
                                            SELECT DISTINCT
                                                compo
                                            FROM
                                                CPC
                                            WHERE
                                                MAR =
                                                    (
                                                        CASE
                                                            WHEN MAR = 'CF'
                                                                THEN
CPC.parent::text
                                                            WHEN MAR = 'CR'
                                                                THEN
CPC.parent::text
                                                            WHEN MAR.type4
= 'SI'
                                                                THEN
CPC.parent::text
                                                        END
                                                    )
                                        )
                                )
                        )
                END
            )
        FROM
            MAR, BIP
        WHERE
            BIP = (MAR)::int
    )
;

after pgFormatter , the new source is :

INSERT INTO tt_tmp_wrk1 (
    SELECT
        MAR,
        (
            CASE WHEN MAR = 'c6hZE..9G_' THEN
                (
                    SELECT
                        COUNT(*)
                    FROM
                        CUS
                    WHERE
                        MAR.obj4 = (
                            CASE WHEN MAR.type4 = 'DccgIKft0q' THEN
                                CUS.account_no::text
                            WHEN MAR.type4 = 'XYoqbXZsxC' THEN
                                CUS.owning_account_no::text
                            WHEN MAR.type4 = '2d_DVgol9Y' THEN
                                CUS.point_origin
                            END)
                        AND (CUS = ( SELECT DISTINCT
                                    DD
                                FROM
                                    PCM
                                WHERE
                                    PCM.compo = CMF.compo)
                                OR CUS = ( SELECT DISTINCT
                                        CPIR
                                    FROM
                                        PCM
                                    WHERE
                                        PCM.compo = CMF.compo)
                                    OR CUS = ( SELECT DISTINCT
                                            CPIR
                                        FROM
                                            PCM,
                                            CT,
                                            PIDR,
                                            CPIR,
                                            CMF
                                        WHERE
                                            PCM.compo = CMF.compo)))
                            WHEN MAR = 'PJXGm_7wpI' THEN
                                (
                                    SELECT
                                        COUNT(*)
                                    FROM
                                        CPC
                                    WHERE
                                        MAR.obj4 = (
                                            CASE WHEN MAR.type4 =
'DccgIKft0q' THEN
                                                CPC.parent::text
                                            WHEN MAR.type4 = 'XYoqbXZsxC' THEN
                                                CPC.parent::text
                                            WHEN MAR.type4 = '2d_DVgol9Y' THEN
                                                CPC.parent::text
                                            END)
                                        AND CPC IN (
                                            SELECT
                                                SPT
                                            FROM
                                                SPT
                                            WHERE
                                                SPT = 'PJXGm_7wpI'
                                                AND SPT IN ( SELECT DISTINCT
                                                        compo
                                                    FROM
                                                        CPC
                                                    WHERE
                                                        MAR = (
                                                            CASE WHEN
MAR = 'DccgIKft0q' THEN
                                                                CPC.parent::text
                                                            WHEN MAR =
'XYoqbXZsxC' THEN
                                                                CPC.parent::text
                                                            WHEN
MAR.type4 = '2d_DVgol9Y' THEN
                                                                CPC.parent::text
                                                            END))))
                                        END)
                                FROM
                                    MAR,
                                    BIP
                                WHERE
                                    BIP = (MAR)::int);

[darold]

Commit 2e0f62c solves this issue.