object names are disrupted

[coffee-with-hoarfrost]

hello all,

Postgresql allows using char $ with object names. The char $ can be used in variable names or in table, column names. In this case pg_format makes dangerous changes. Example is below.

Before:

DROP VIEW IF EXISTS vw$test$pg_format;
DROP TABLE IF EXISTS tbl$test$pg_format;
--
CREATE TABLE tbl$test$pg_format (str$clmn$column_1 TEXT, str$clmn$column_2 TEXT);
CREATE VIEW vw$test$pg_format AS
SELECT str$clmn$column_1, str$clmn$column_2
FROM tbl$test$pg_format;

After:

DROP VIEW IF EXISTS vw$test $ pg_format;

DROP TABLE IF EXISTS tbl$test $ pg_format;

--
CREATE TABLE tbl$test $ pg_format (
    str$clmn $ column_1 text,
    str$clmn $ column_2 text
);

CREATE VIEW vw$test $ pg_format AS
SELECT
    str$clmn $ column_1,
    str$clmn $ column_2
FROM
    tbl$test $ pg_format;

[darold]

This looks easy to solve but it is not. The problem is that the dollar sign is more often used as the code separator for function or to replace the single quote in string definition. pgFormatter can not distinguish between and that will break constant or code part detection. IMHO, dollar sign are like accent, they should be avoid in object name outside that they can confuse some programs it doesn't help reading. But I agree that they are supported in standard SQL.

Anyway as a workaround you can try using -S '$', in the case above it will work but it will give incorrect results other cases, this option is not done for that.

$ perl pg_format -S '$' 
DROP VIEW IF EXISTS vw$test$pg_format;
DROP TABLE IF EXISTS tbl$test$pg_format;
CREATE TABLE tbl$test$pg_format (str$clmn$column_1 TEXT, str$clmn$column_2 TEXT);
CREATE VIEW vw$test$pg_format AS
SELECT str$clmn$column_1, str$clmn$column_2
FROM tbl$test$pg_format;
DROP VIEW IF EXISTS vw$test$pg_format;
^D

DROP TABLE IF EXISTS tbl$test$pg_format;

CREATE TABLE tbl$test$pg_format (
    str$clmn$column_1 text,
    str$clmn$column_2 text
);

CREATE VIEW vw$test$pg_format AS
SELECT
    str$clmn$column_1,
    str$clmn$column_2
FROM
    tbl$test$pg_format;