Thread: cli in sql?
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?
am 11.11.2005, um 8:57:14 -0500 mailte Frank Bax folgendes: > 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 You can use any untrusted procedural language such as plpgperlu or plsh to determine this. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
> 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');
--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200511111457
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDdPkRvJuQZxSWSsgRAufUAJ9f4/IaYrJDMi3Yg74x0tkN4tmUcQCgmlu9
wAkqRHgYQY9DtdIIfH/g7xY=
=n/J7
-----END PGP SIGNATURE-----
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,
compiledby 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.
Frank Bax <fbax@sympatico.ca> writes: > 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 PG 7.4 doesn't understand $$ quotes --- you need to convert the function body to old-style quoting. Or upgrade ;-) See http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS for details, esp. section 4.1.2.2. regards, tom lane