pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcli in sql?
5+ messages / 4 participants
[nested] [flat]
* cli in sql?
@ 2005-11-11 13:57 Frank Bax <fbax@sympatico.ca>
0 siblings, 3 replies; 5+ messages in thread
From: Frank Bax @ 2005-11-11 13:57 UTC (permalink / raw)
To: pgsql-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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cli in sql?
@ 2005-11-11 16:09 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Frank Bax <fbax@sympatico.ca>
2 siblings, 0 replies; 5+ messages in thread
From: A. Kretschmer @ 2005-11-11 16:09 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cli in sql?
@ 2005-11-11 20:03 Greg Sabino Mullane <greg@turnstep.com>
parent: Frank Bax <fbax@sympatico.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2005-11-11 20:03 UTC (permalink / raw)
To: pgsql-sql; +Cc: fbax@sympatico.ca
-----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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cli in sql?
@ 2005-11-11 21:31 Frank Bax <fbax@sympatico.ca>
parent: Frank Bax <fbax@sympatico.ca>
2 siblings, 1 reply; 5+ messages in thread
From: Frank Bax @ 2005-11-11 21:31 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cli in sql?
@ 2005-11-11 23:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-11-11 23:27 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-11-11 23:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-11 13:57 cli in sql? Frank Bax <fbax@sympatico.ca>
2005-11-11 16:09 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-11-11 20:03 ` Greg Sabino Mullane <greg@turnstep.com>
2005-11-11 21:31 ` Frank Bax <fbax@sympatico.ca>
2005-11-11 23:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox