Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Erwin Brandstetter
Date:
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;



Regards
Erwin

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

From
Pavel Stehule
Date:
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>:
> 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;
>
>
>
> Regards
> Erwin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

From
Erwin Brandstetter
Date:
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

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

From
Pavel Stehule
Date:
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

>
> Regrads
> Erwin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

From
"Greg Sabino Mullane"
Date:
-----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-----

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

From
Erik Jones
Date:
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