Re: How to get text for a plpgsql variable from a file.

-----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(at)turnstep(dot)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-----