Le Mercredi 11 Février 2004 12:12, Erwin Van de Velde a écrit : > If anyone has built such functions already, I'd gladly accept, and you can > win a line in my thank word ;-)
Dear Erwin, I built a small centralised database built for Ulogd and ran into the same questions. You can either use implicit or explicit CASTs: SELECT '192.168.0.3'::inet AS myexplicitcast will convert a string into an inet Sometimes, when you do not have contol over the logging deamon sending queries (which is the case for example of Ulogd), you may be obliged to use implicit CASTs (a solution described in PostgreSQL bits): CREATE OR REPLACE FUNCTION ulog_timecast(int4) RETURNS timestamp AS 'select "timestamp"($1::abstime);' LANGUAGE 'sql' VOLATILE; CREATE CAST (int4 AS timestamp) WITH FUNCTION ulog_timecast(int4) AS IMPLICIT; In the end, you may need to add fields to your table and compute the logs when they are received, using triggers and procedures (better STABLE ones). But this can slow down logging. You may also prefer to run cron jobs (my volume is too small for such optimisations). Also, do not forget using partial indexes. Using PostgreSQL on a double-athlon server, I can log up to 1000 messages every second, but I did not try to stress the server too long (it should not be a problem with partial indexing). If you are interested in my code, just drop me an email and I will send you the dump (just a few functions and triggers). Next week, I plan to use PLbash to be able to send IPTABLES scripts to my firewall interactively. This could make PostgreSQL one of the only database able to counter attack on the fly during data acquisition. Also, in order to write fast server-side applications, do not hesitate to try pgAdmin III from http://www.pgadmin.org. This will give you direct access to the list of CASTs. pgAdmin III has a large number of very handy features to write server-side applications. Cheers, Jean-Michel ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
