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

hello

look on orafce from pgfoundry. There modul utl_file

http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE

Regards
Pavel Stehule

2009/12/16 Erwin Brandstetter <brsaweda@gmail.com>:

On Dec 16, 10:47 pm, pavel.steh...@gmail.com (Pavel Stehule) wrote:

hello

look on orafce from pgfoundry. There modul utl_file

http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE

Thanks Pavel, that should do the trick.

I assume then, there is no easier built-in way in standard postgres?

Regrads
Erwin

2009/12/17 Erwin Brandstetter <brsaweda@gmail.com>:

On Dec 16, 10:47 pm, pavel.steh...@gmail.com (Pavel Stehule) wrote:

hello

look on orafce from pgfoundry. There modul utl_file

http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE

Thanks Pavel, that should do the trick.

I assume then, there is no easier built-in way in standard postgres?

PostgreSQL 8.3 and higher can read file from pg data directory. That is all

Regards
Pavel

On Dec 16, 2009, at 11:19 AM, Erwin Brandstetter wrote:

Hello,

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:

CREATE FUNCTION test() RETURNS void AS
$BODY$
DECLARE
mytxt text;
BEGIN

CREATE TEMP TABLE x (x text);
COPY x from '/path/to/myfile.txt';
mytxt := (SELECT x from x);

...

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

If you're allowed, you can use an untrusted procedural language.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

-----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-----