1

I'm working with PL/pgSQL and Orafce after a conversion from Oracle. In a function I'm writing in a file. I would like to write with the LATIN1 encoding. My database is in UTF8.

There is convert functions but I think I miss something because I don't seems to find my use case.

convert('testé', 'UTF8', 'LATIN1') ==> in BYTEA test\351

The problem is that this function returns bytea. How could I get a text?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Martin Dejax
  • 11
  • 1

1 Answers1

0

I think that you would actually need the convert_to function that converts a text from the database encoding to a bytea encoded in a different encoding:

SELECT convert_to('testé', 'LATIN1');

  convert_to  
--------------
 \x74657374e9
(1 row)

You cannot have the result to be a text, because that byte sequence would not be a valid UTF-8 encoded string, and PostgreSQL is unforgiving about this kind of data corruption.

Unfortunately orafce doesn't seem to offer a function to write binary data to a file, but perhaps COPY can help you:

COPY (SELECT textdata FROM texttable ORDER BY something)
   TO 'latin1file' (ENCODING 'LATIN1', FORMAT 'csv');

The only disadvantage is that COPY escapes certain characters like " and ,, so you might need to choose a separator or delimiter that does not appear in your strings.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yes I also tried convert_to, unfortunatly I have the same problem with bytea at the end. for the COPY function, I used it to export CSV files, but in my case, I rely on UTL_FILE.FILE_TYPE multiple times in plpgsql functions, so how could I write in a FILE_TYPE with the COPY function ? – Martin Dejax Nov 10 '20 at 12:58
  • You won't be able to do it with orafce, because it does not support writing binary data, rigt? – Laurenz Albe Nov 10 '20 at 13:35
  • Yes, so that's why I followed your advice, and I'll use the COPY command like that: `command := concat('COPY (',vl_param_query,') TO PROGRAM ''cat>>', vl_param_rapport_path, ''' WITH (ENCODING ''LATIN1'')');` which is working fine. but the function accept 2 parameters, file UTL_FILE.FILE_TYPE and content text. So now I have to find a way the get the filepath from UTL_FILE.FILE_TYPE which doesnt seems to be possible – Martin Dejax Nov 10 '20 at 14:12
  • What is the `FILE_TYPE`? – Laurenz Albe Nov 10 '20 at 14:26
  • Seems like it is just an integer representing a file, so my function look like that `prc_put_line (vl_param_rapport UTL_FILE.FILE_TYPE, vl_param_string text )` and have to add a line to the report. So my workaround at the moment is to add the path of the file like that `set_config('utl_file.' || vl_outputfile, filepath, false);` in the function calling the prc_put_line function and get it back in the prc_put_line function with `current_setting('utl_file.' || vl_param_rapport)`, but I'd rather find a more elegant solution – Martin Dejax Nov 10 '20 at 14:49
  • You could write your own C function, that might be easiest... – Laurenz Albe Nov 10 '20 at 15:01
  • Yes thank you, that's a good idea, I'll dig into that – Martin Dejax Nov 10 '20 at 16:06