How to fix 22P06: nonstandard_use_of_escape_character in PostgreSQL

-- Problematic Unicode escapes
SELECT E'Greek alpha: \u3B1';  -- Missing digit (should be 4 hex digits)
SELECT E'Smiley: \U1F600';     -- Wrong: should be 8 hex digits for \U
SELECT E'Letter: \u03b1';      -- Wrong: lowercase hex digits not always supported

-- Correct Unicode escapes
SELECT E'Greek alpha: \u03B1';  -- 4 hex digits, uppercase
SELECT E'Smiley: \U0001F600';   -- 8 hex digits for \U escapes
SELECT E'Letter: \u03B1';       -- Uppercase hex digits (A-F, 0-9)

-- In PL/pgSQL functions:
CREATE OR REPLACE FUNCTION test_unicode() RETURNS text AS $$
BEGIN
  -- Correct:
  RETURN E'Unicode: \u03B1\u03B2\u03B3';  -- αβγ
  -- Wrong:
  -- RETURN E'Unicode: \u3b1';  -- Missing digit, lowercase
END;
$$ LANGUAGE plpgsql;

-- Rules for Unicode escapes:
-- 1. \u must be followed by exactly 4 hexadecimal digits (0-9, A-F)
-- 2. \U must be followed by exactly 8 hexadecimal digits
-- 3. Digits should be uppercase (A-F, not a-f) for consistency
-- 4. Valid Unicode code points only (check unicode.org)
-- 5. Use E'...' string syntax for escape sequences

-- Problematic hex/octal escapes
SELECT E'Byte: \xG';     -- 'G' is not a hex digit
SELECT E'Byte: \x';      -- Missing hex digits
SELECT E'Byte: \x123';   -- Too many digits (max 2)
SELECT E'Octal: \89';    -- '8' and '9' not valid octal digits
SELECT E'Octal: \';      -- Incomplete escape

-- Correct hex/octal escapes
SELECT E'Byte: \x41';     -- 'A' (hex 41)
SELECT E'Byte: \x0A';     -- Newline (hex 0A)
SELECT E'Byte: \x00';     -- Null byte
SELECT E'Octal: \101';    -- 'A' (octal 101)
SELECT E'Octal: \012';    -- Newline (octal 12)
SELECT E'Octal: \0';      -- Null byte (octal 0)

-- In PL/pgSQL:
CREATE OR REPLACE FUNCTION test_hex_octal() RETURNS text AS $$
DECLARE
  result text;
BEGIN
  -- Correct usage:
  result := E'Hex: \x48\x65\x6C\x6C\x6F';  -- "Hello"
  result := result || E' Octal: \110\145\154\154\157';  -- "Hello"
  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Rules:
-- Hexadecimal escapes (\xXX):
--   - Exactly 2 hexadecimal digits (0-9, A-F, a-f)
--   - Represents a single byte
--   - Case insensitive for hex digits

-- Octal escapes (\ooo):
--   - 1-3 octal digits (0-7)
--   - Maximum value \377 (decimal 255)
--   - Shorter sequences allowed: \0, \12, \123

-- Both require E'...' string syntax

-- Problematic: Mixed escape sequences
CREATE OR REPLACE FUNCTION problematic_escape() RETURNS text AS $$
DECLARE
  part1 text := E'\u03B1';      -- Unicode escape
  part2 text := '\x62';         -- Hex escape without E'...'
  part3 text := E'\n';          -- Newline
BEGIN
  RETURN part1 || part2 || part3;  -- Mixing escape types
END;
$$ LANGUAGE plpgsql;

-- Solution: Standardize all strings
CREATE OR REPLACE FUNCTION fixed_escape() RETURNS text AS $$
DECLARE
  part1 text := E'\u03B1';      -- Unicode
  part2 text := E'\x62';        -- Hex with E'...'
  part3 text := E'\n';          -- Newline
  dynamic_sql text;
BEGIN
  -- For dynamic SQL, be careful with escapes
  dynamic_sql := format('SELECT %L', E'Text with \n newline');
  -- format() handles escaping properly

  RETURN part1 || part2 || part3;
END;
$$ LANGUAGE plpgsql;

-- Using dollar-quoted strings to avoid escape issues
CREATE OR REPLACE FUNCTION dollar_quoted_example() RETURNS text AS $$
DECLARE
  -- Dollar-quoted strings don't process backslash escapes
  no_escape text := $$Raw string with ackslash and 'quote'$$;

  -- But you can combine with E'...' if needed
  with_escape text := E'\u03B1' || $$ and raw text$$;
BEGIN
  RETURN with_escape;
END;
$$ LANGUAGE plpgsql;

-- Best practices:
-- 1. Use E'...' consistently for all strings needing escapes
-- 2. Avoid mixing escaped and non-escaped string concatenation
-- 3. Use format() or quote_literal() for dynamic SQL
-- 4. Consider dollar-quoted strings for complex string literals
-- 5. Test escape sequences in simple SELECT statements first

-- Check relevant settings
SHOW standard_conforming_strings;
SHOW escape_string_warning;
SHOW backslash_quote;

-- standard_conforming_strings = on (default since 9.1):
-- - Backslashes are literal in regular strings
-- - Use E'...' for escape sequences
-- - Recommended for new code

-- standard_conforming_strings = off:
-- - Backslashes are escape characters in all strings
-- - Legacy behavior for compatibility
-- - Can cause confusion with escape sequences

-- If migrating legacy code:
SET standard_conforming_strings = off;  -- Temporary
-- Update code to use E'...' for escapes
SET standard_conforming_strings = on;   -- Back to default

-- escape_string_warning = on (default):
-- - Warns about backslashes in regular strings
-- - Helps identify escape sequence issues

-- backslash_quote settings:
-- - Controls whether \' is allowed in strings
-- - Affects escape sequence parsing

-- For consistent behavior:
SET standard_conforming_strings = on;
SET escape_string_warning = on;

-- Update your PL/pgSQL functions:
-- Old style (with standard_conforming_strings = off):
-- CREATE FUNCTION old() RETURNS text AS $$
-- BEGIN
--   RETURN 'Line 1
Line 2';  -- Backslash escape
-- END;
-- $$ LANGUAGE plpgsql;

-- New style:
CREATE OR REPLACE FUNCTION new() RETURNS text AS $$
BEGIN
  RETURN E'Line 1\nLine 2';  -- Explicit escape string
END;
$$ LANGUAGE plpgsql;

-- Or use dollar-quoted strings:
CREATE OR REPLACE FUNCTION dollar() RETURNS text AS $$
BEGIN
  RETURN $$Line 1
Line 2$$;  -- Actual newline in source
END;
$$ LANGUAGE plpgsql;

-- Test individual escape sequences
SELECT E'\u03B1' AS test1;      -- Should work: α
SELECT E'\x41\x42\x43' AS test2;  -- Should work: ABC
SELECT E'\101\102\103' AS test3;  -- Should work: ABC (octal)

-- Test problematic sequences (will error)
-- SELECT E'\u3B1' AS bad1;      -- Error: missing digit
-- SELECT E'\xGH' AS bad2;       -- Error: invalid hex digit
-- SELECT E'\89' AS bad3;        -- Error: invalid octal digit

-- Test in PL/pgSQL context
CREATE OR REPLACE FUNCTION test_escapes() RETURNS text AS $$
DECLARE
  test_result text;
BEGIN
  -- Test each escape type
  test_result := E'Unicode: \u03B1\u03B2\u03B3';
  test_result := test_result || E' Hex: \x48\x65\x6C\x6C\x6F';
  test_result := test_result || E' Octal: \110\145\154\154\157';
  test_result := test_result || E' Standard: \tTab\nNewline';

  RETURN test_result;
EXCEPTION
  WHEN SQLSTATE '22P06' THEN
    RETURN 'ERROR: Invalid escape sequence';
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT test_escapes();

-- Check function definition for escape issues
\df+ test_escapes

-- Use pg_get_functiondef to see the source
SELECT pg_get_functiondef('test_escapes'::regproc);

-- For dynamic testing, use DO blocks
DO $$
BEGIN
  -- This will error if escapes are invalid
  PERFORM E'\u03B1';
  RAISE NOTICE 'Unicode escape test passed';
EXCEPTION
  WHEN SQLSTATE '22P06' THEN
    RAISE WARNING 'Unicode escape test failed';
END;
$$;

-- Create a test table with various string literals
CREATE TEMP TABLE escape_test AS
SELECT
  E'\u03B1' AS unicode_escape,
  E'\x41' AS hex_escape,
  E'\101' AS octal_escape,
  E'\n' AS std_escape;

-- Common migration scenarios:

-- 1. Oracle-style escapes (q'[...]')
-- Oracle: q'[It's raining]'
-- PostgreSQL: $$It's raining$$ or E'It\'s raining'

-- 2. MySQL escapes (different Unicode syntax)
-- MySQL: _utf8'α' or N'α'
-- PostgreSQL: E'\u03B1' or U&'\03B1'

-- 3. SQL Server Unicode literals (N'...')
-- SQL Server: N'α'
-- PostgreSQL: E'\u03B1'

-- 4. JavaScript/Python-style escapes
-- JavaScript: '\u03B1' (always works)
-- Python: '\u03B1' or '\x41'
-- PostgreSQL: E'\u03B1' or E'\x41' (with E'...')

-- Migration function example
CREATE OR REPLACE FUNCTION migrate_escape(input_text text) RETURNS text AS $$
BEGIN
  -- Replace common non-standard patterns
  -- Example: Convert \u{03B1} to \u03B1
  input_text := regexp_replace(input_text, '\\u\{([0-9A-Fa-f]{4})\}', '\\u\1', 'g');

  -- Convert \x{41} to \x41
  input_text := regexp_replace(input_text, '\\x\{([0-9A-Fa-f]{2})\}', '\\x\1', 'g');

  -- Ensure E'...' prefix if not present and has escapes
  IF input_text ~ '\\\\(u[0-9A-Fa-f]{4}|U[0-9A-Fa-f]{8}|x[0-9A-Fa-f]{2}|[0-7]{1,3}|[abfnrtv])' THEN
    IF NOT input_text LIKE 'E''%' THEN
      input_text := 'E''' || input_text || '''';
    END IF;
  END IF;

  RETURN input_text;
END;
$$ LANGUAGE plpgsql;

-- Test migration
SELECT migrate_escape('\u{03B1}');  -- Returns E'\u03B1'
SELECT migrate_escape('\x{41}');    -- Returns E'\x41'
SELECT migrate_escape('Line 1\nLine 2');  -- Returns E'Line 1\nLine 2'

-- For bulk migration of PL/pgSQL functions:
-- 1. Extract function definitions
-- 2. Apply migrate_escape() to string literals
-- 3. Recreate functions with corrected escapes

-- Use pg_dump and sed/awk for bulk updates:
-- pg_dump --schema-only -t 'function_name' | sed "s/old_escapes/new_escapes/g"

-- Or create a migration script:
/*
DO $$
DECLARE
  func_record RECORD;
  old_def text;
  new_def text;
BEGIN
  FOR func_record IN
    SELECT proname, pg_get_functiondef(oid) AS definition
    FROM pg_proc
    WHERE pronamespace = 'public'::regnamespace
  LOOP
    old_def := func_record.definition;
    new_def := migrate_escape(old_def);  -- Your migration logic
    IF old_def != new_def THEN
      EXECUTE 'DROP FUNCTION ' || func_record.proname;
      EXECUTE new_def;
    END IF;
  END LOOP;
END;
$$;
*/