WPF PostgreSQL 9.6 C#
After spending the better of today reading what is on the Internet, I still haven't figured out how this is done.
I am trying to encode a "format" tag before a text string. Something like:
\u0002 (This is the formating area) \u0003 "This is the text string" \u0002 (This is another formating area ) \u0003 "This is another text string" (.....)
That is, each formating area is enclosed by some control-code (i.e., non-printable character) followed by a text message. This then will be repeated for as many messages as I need.
What I want to do ( I don't know how) is to send this entire block of contol-code+message to a WCF method which will then send it to the PostgreSQL backend.
Getting it to WCF is easy, but what is the best method of storing this in PostgreSQL?
Once stored, I need to return it to the client at which time I am hoping to use a two part parser (probably with Regex) to first break the block down into sub-strings of [format]+[Message] and then ultimately to apply each format to its attached message. (Think for loop).
But I do not understand how to get control-codes in an out of PostgreSQL such that I can use Regex to isolate each [format]+Message string back on the client.
Any help or guidance would be much appreciated.
TIA.
With recent versions of PostgreSQL, you can use \uXXXX or \UXXXXXXXX escape sequences in string literals if you precede the string literal with E to signify that it contains escape sequences:
SELECT E'\u0002part 1\u0003\part 2';
┌──────────────────────┐
│ ?column? │
├──────────────────────┤
│ \x02part 1\x03part 2 │
└──────────────────────┘
(1 row)
It is easy to split a string in parts along these lines, see the documentation about PostgreSQL's string functions.
For example, if you want to split such a string into an array at either \u0002 or \u0003, you could do it like this:
SELECT regexp_split_to_array(
E'\u0002part 1\u0003\part 2',
E'[\u0002\u0003]'
);
┌────────────────────────┐
│ regexp_split_to_array │
├────────────────────────┤
│ {"","part 1","part 2"} │
└────────────────────────┘
(1 row)
For newbies, (like myself), coming here, a close reading of the PgAdmin Documentation shows that the behavior of PgAdmin depends highly on the column definition used in PostgreSQL. PostgreSQL documentation (if I read it correctly) states that pretty much any encoding can be called a "text" field since internally PostgreSQL defaults to UTF-8 encoding.
By way of example,
CREATE TABLE progress_note_drawing_transcription
(
encounter_recid integer NOT NULL,
transcription text,
strokes bytea
)
insert into progress_note_drawing_transcription (encounter_recid, transcription, strokes)
values (111421,E'\u0009 This is a message',E'\u0009 This is a message')
But note the following differences:
When the column of postreSQL is defined as "text", e.g, "transcription" is a text column, then
select transcription from progress_note_drawing_transcription;
will show " This is a message" where the leading \u0009 is replaced by a space.
When the column of PostgreSQL is defined as bytea, as in "strokes", then PgAdmin will show:
select strokes from progress_note_drawing_transcription;
as "\011 This is a message"
where 9 is written as an escape octal (base 8).
Furthermore, to see the straight "hex" values, the "text" field in PgAdmin requires:
SELECT encode(convert_to(transcription, 'UTF-8'), 'hex') FROM progress_note_drawing_transcription;
whereas the "bytea" field can be simply seen as:
SELECT encode(strokes, 'hex') FROM progress_note_drawing_transcription;
I hope this helps somebody.
(So I guess which is used dependes mostly on personal preference with the one caveat being I think read operations on the "text" data type in postgreSQL is about 15% faster then "bytea" data types. I could be wrong though.)