awslabs / awslabs/amazon-redshift-utils
v_generate_tbl_ddl: Apostrophes not escaped in table comments
- Dominant language
- Python
- Stars
- 2.8k
- Forks
- 1.2k
- PR merge metrics
- No merged PRs in 30d
Description
I have a multi-line table comment, such as
```sql
COMMENT ON TABLE foo IS '
Roses are red
Violets are blue
Redshift doesn''t mind long comments
And so shouldn''t you
';
```
When this is exported via `v_generate_tbl_ddl`, it comes out as:
```sql
COMMENT ON table foo IS '
Roses are red
Violets are blue
Redshift doesn't mind long comments
And so shouldn't you
';
```
Notice that both contractions are no longer correctly doubling up `''`, which results in a syntax error when I try to run the generated DDL. I expected the generated DDL to correctly escape these quotes in a comment.
I haven't had time to look into this, including:
* Whether it matters that there are exactly a balanced pair of apostrophes, or whether this would work with just one apostrophe, or if you just need more than one apostrophe
* Whether it matters that it's multi-line
* Whether it could affect e.g. column comments
Thanks for giving us a way to get DDL out of Redshift!
Contributor guide
Research direction
Start with the v_generate_tbl_ddl view and reproduce the issue using the multi-line COMMENT ON TABLE example in the report. Check whether the generated DDL preserves doubled apostrophes, including in column comments if that view handles them; done means the exported statement runs without a syntax error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 50/100