[syslog-ng] SQL destination - inserting data into ARRAY column

[Tomáš Novosad]

Hello everyone,

i'm using syslog-ng to parse/store some logs into PostgreSQL database.
I've got full control of the data being logged, because it is produced in my perl code.
Data are logged into syslog and written unchanged into file, and in separate log statement,
data are parsed into macros and inserted into database.

There is a column which hold a list of email addresses. The column in database is defined as array of text (mail_to TEXT[]).
This works fine, as the value is being parsed into macro, and this is enclosed in brackets.
In detail log statement looks like:
mail_to="addr1,addr2"
which is parsed with pattern db into a macro ${mflog.to}
then the macro is surrounded by brackets - this is done with set statement:
set("{${mfstat.to}}", value("mfstat.to"));
Syslog then insert this value into postgres:'{addr1,addr2}'
Postgres successfuly parses the string and store it as two-element array
[addr1,addr2].

This works just fine, but now to my problem.
I also want to store another ARRAY colum, which is not of TEXT type, but
a custom data type. The definition in database looks like:
(parts part_data[])
where part_data is my custom type defined in postgres.

Problems comes when i try to insert such data via syslog.
My perl program produces this line which is sent to syslog:
parts="(part1,20,bla),(part2,234,bli)"
this is again parsed into macro and macro is again surronded by brackets.
So syslog tries to insert this
'{(part1,20,bla),(part2,234,bli)}'
which fails because target column if of type part_data[], but postgres parses the string as text[].
Therfore i need to explicitly instruct postgres to do a data conversion.
This can be easily achieved by adding the conversion to input string
'{(part1,20,bla),(part2,234,bli)}'::part_data[]

however i do no know how to achieve this, as the literal text
'::part_data[]' is just outside the single qutes.
I just do not know how to instruct syslog-ng to add extra text
::part_data[]
just after the last single quote.
All the inserted values are simple strings and i do not know how to add some extramodifications to that strings.

Is there any way to do such thing?

Similar thing would be for example running some function on inserted value.
Let's say i would like the insert to look like this
"INSERT into table (col1) VALUES (some_postgres_func(val1))"
How do i set the sql destination in syslog-ng to achieve this?

Many thanks for any ideas.
-- 
Tomáš Novosad