Hi Johan,
it's not easy because you need to process the VCALENDAR/VCARD attributes and then perform a search in the processed data (which often needs to unescape and several other things).
Here is an example to extract the SUMMARY attribute text value from VCALENDAR (including unescaping, etc.), and then compare the extracted attribute value with any value you are looking for:
SELECT dav_name, caldav_data FROM (SELECT *, regexp_replace(regexp_replace((regexp_matches(regexp_replace(caldav_data, '\r\n[\t ]', '', 'g'), '\r\nSUMMARY:(.*)\r\n', 'n'))[1], '\n', E'\n', 'g'), '\([,;\])', '\1', 'g') AS caldav_attr FROM caldav_data) AS _ WHERE caldav_attr LIKE '%abcdefghijklmnopqrstuvwxyz%';
in short: regexp_replace(XXX, '\r\n[\t ]', '', 'g') => remove line folding from VCALENDAR/VCARD (regexp_matches(XXX, , '\r\nSUMMARY:(.*)\r\n', 'n'))[1] => find the SUMMARY attribute and extract the attribute value regexp_replace(XXX, , '\n', E'\n', 'g') => replace the \n string with real newline (newline is not allowed because it is used in the VCALENDAR/VCARD format itself) regexp_replace(XXX, , '\([,;\])', '\1', 'g') => unescape the , ; and \ characters (these are escaped in text values)
the result is the whole caldav_data table with one additional column (caldav_attr) which contains the real (raw) text value from the SUMMARY attribute.
Then the outer select allows you to select all data (from the extended caldav_data table) which met your condition (in my case all object where the SUMMARY contains "abcdefghijklmnopqrstuvwxyz").
This solution is not optimal, but it does exactly what you asked :-) ... for other attributes you need to modify the "(regexp_matches(XXX, , '\r\nSUMMARY:(.*)\r\n', 'n'))[1]" part ...
JM
On 29 Apr 2017, at 14:37, Johan Vromans jvromans@squirrel.nl wrote:
Just wondering...
Do you have (command line or SQL) scripts to perform simple tasks with the DAViCal server? For example get a list of all appointments that match a specific topic? Or a list of all email addresses in the contacts database?