Re: Using file data as argument to functions called from psql command line

2009/11/19 Mazen Abdel-Rahman <saba(dot)mazen(at)gmail(dot)com>:
> Hi All,
> Is it possible possible to use a file's data as an argument to one of
> PostgreSQL's functions via the psql command line?
> For example - I know I can do this:
> select xpath('/bookstore/book','<?xml version="1.0"
> encoding="ISO-8859-1"?><bookstore><book><title lang="eng">Harry
> Potter</title><price>29.99</price></book></bookstore>')
>
> Is it possible to read a file to be the second argument in this function?
> (For example books.xml).
> Thank You!
> Mazen Abdel-Rahman

You can write a wrapper function around xpath in pl/perlu (untrusted
pl/perl - http://www.postgresql.org/docs/8.4/static/plperl-trusted.html)
and use file-access commands to read in the file then pass to the
xpath function. However, this introduces security implications, hence
the untrusted name. It is not recommended to use file-system access
directly within queries or functions. If possible, use the COPY
command to import files into tables first.
(http://www.postgresql.org/docs/8.4/static/sql-copy.html)

Regards

Thom