At 03:03 PM 11/11/05, Greg Sabino Mullane wrote:
At 08:57 AM 11/11/05, Frank Bax wrote:
> If my database has column containing a filename, can I use sql to present
> this filename and datemodified (as output from 'ls -l' or from mtime()
> fuction) or *must* it be done after the query in interface such as php
or perl?
Neither. You can do it inside the db with a "pl" language such as plperlu:
CREATE OR REPLACE FUNCTION filemodtime(TEXT) RETURNS TEXT LANGUAGE plperlu AS
$$
my $filename = shift;
-e $filename or elog(ERROR, qq{The file "$filename" does not exist\n});
return localtime($^T - (60*60*24* -M _));
$$;
SELECT filemodtime('/var/log/messages');
SELECT filemodtime('/dark/matter');
This looks interesting! But I'm not sure how to use it?
wbax=> select version();
version
---------------------------------------------------------------------
PostgreSQL 7.4.3 on i386-unknown-openbsd3.6, compiled by GCC 2.95.3
(1 row)
wbax=> CREATE OR REPLACE FUNCTION filemodtime(TEXT) RETURNS TEXT LANGUAGE
plperlu AS
wbax-> $$
wbax-> my $filename = shift;
ERROR: syntax error at or near "$" at character 80
wbax=> -e $filename or elog(ERROR, qq{The file "$filename" does not exist\n});
Invalid command \n});. Try \? for help.
wbax(> return localtime($^T - (60*60*24* -M _));
wbax(> $$;
wbax(>
My system does have
/usr/local/lib/postgresql/plperl.so
And I tried changing plperlu to plperl, but get the same error. I found:
http://www.netcraft.com.au/geoffrey/postgresql/plperl.html
to add the language to my system, but that didn't help either.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
