Обсуждение: 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