-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
NotDashEscaped: You need GnuPG to verify this message
> I need a long text form from a file in my plpgsql variable.
> Can anyone think of a more straightforward way to read the file than
> the following:
Sounds like a job for an 'untrusted' procedural language.
Here's a quick example using plperlu:
===
\t
\o /tmp/gtest
SELECT 'OH HAI';
\o
\t
CREATE OR REPLACE FUNCTION
read_file(TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $bc$
use strict;
use warnings;
my $filename = shift;
index($filename, '/')
and die qq{File name must be an absolute path\n};
open my $fh, '<', $filename
or die qq{Could not open file "$filename": $!\n};
my $string;
{
local $/;
$string = <$fh>;
}
close $fh
or die qq{Could not close file "$filename": $!\n};
return $string;
$bc$;
CREATE OR REPLACE FUNCTION gtest()
RETURNS TEXT
LANGUAGE plpgsql
AS $bc$
DECLARE
external_file_contents TEXT;
BEGIN
SELECT INTO external_file_contents read_file('/tmp/gtest');
RETURN 'GOT:' || external_file_contents;
END
$bc$;
SELECT gtest();
===
Piping all of the above into psql gives:
Output format is unaligned.
Showing only tuples.
Tuples only is off.
Output format is aligned.
CREATE FUNCTION
CREATE FUNCTION
gtest
------------
GOT:OH HAI
--
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 200912170920
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAksqPrkACgkQvJuQZxSWSsgjFACfebEHE6rLGs04w6mptctG7nuI
IXwAoJmLOwavrXyaU+4lHx3OsIws4JOc
=58sb
-----END PGP SIGNATURE-----
