Message-Id: <5.2.1.1.0.20051111161921.05972bf0@pop6.sympatico.ca>
Date: Fri, 11 Nov 2005 16:31:35 -0500
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: cli in sql?
In-Reply-To: <1bb9fe33ef04362819dae06d5326b2ac@biglumber.com>
References: <5.2.1.1.0.20051111085132.04119120@pop6.sympatico.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

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.