EVL – ETL Tool

Readpg

(since EVL 2.0)

Write to standard output or <f_out> PostgreSQL <table>.

Password is taken:

1. from file ‘$EVL_PASSFILE’, which is by default ‘$HOME/.evlpass’,
2. from file ‘$PGPASSFILE’, which is by default ‘$HOME/.pgpass’.

When such file has not permissions 600, it is ignored! For details see ‘evl-password’.

Readpg

is to be used in EVS job structure definition file. <f_out> is either output file or flow name.

evl readpg

is intended for standalone usage, i.e. to be invoked from command line and writing records to standard output.

EVD and EVS are EVL definition files, for details see evl-evd(5) and evl-evs(5).

Synopsis

Readpg
  [<schema>.]<table> <f_out> (<evd>|-d <inline_evd>)
  [--columns=<col_name>[,<col_name>] | -q|--query=<query>]
  [-b|--dbname=<database>] [-h|--host=<hostname>] [-p|--port=<port>]
  [-u|--username=<pguser>] [--psql=<psql_options>] [-y|--text-output]

evl readpg
  [<schema>.]<table> (<evd>|-d <inline_evd>)
  [--columns=<col_name>[,<col_name>] | -q|--query=<query>]
  [-b|--dbname=<database>] [-h|--host=<hostname>] [-p|--port=<port>]
  [-u|--username=<pguser>] [--psql=<psql_options>] [-y|--text-output]
  [-v|--verbose]

evl readpg
  ( --help | --usage | --version )

Options

-d, --data-definition=<inline_evd>

either this option or <evd> file must be presented. Example: ‘-d 'id int, user_id string(6) enc=iso-8859-1'’

--columns=<column_name>[,<column_name>]

get only specified columns from the table. Example: ‘--columns="id,name,value"’

-q, --query=<query>

Use SQL <query> instead of reading whole table. With this option <table> might be an empty string.

-y, --text-output

write the output as text, not binary

Standard options:

--help

print this help and exit

--usage

print short usage information and exit

-v, --verbose

print to stderr info/debug messages of the component

--version

print version and exit

’psql’ options:

-b, --dbname=<database>

either this or environment variable ‘PGDATABASE’ should be provided, if not, then current system username is used as psql database. If also ‘PGDATABASE’ environment variable is set, this option has preference. (This option is provided to ‘psql’ command.)

-h, --host=<hostname>

either this or environment variable ‘PGHOST’ should be provided when connecting to other host than localhost. If also ‘PGHOST’ variable is set, this option has preference. (This option is provided to ‘psql’ command.)

-p, --port=<port>

either this or environment variable ‘PGPORT’ should be provided when using other than standard port ‘5432’. (This option is provided to ‘psql’ command.)

--psql=<psql_options>

all other options to be provides to psql command. See ‘man psql’ for details.

-u, --username=<pguser>

either this or environment variable ‘PGUSER’ should be provided, if not, then current system username is used as psql user. If variable ‘PGUSER’ is set, this option has preference. (This option is provided to ‘psql’ command.)

Examples

1. To read a table from default schema (mostly ‘public’) in EVL job (i.e. in EVS file) from localhost:5432:

   export PGUSER=some_pg_user
   export PGDATABASE=my_db
   Readpg my_table MYTABLE evd/mytable.evd
   Map    MYTABLE  ...
   

   Password is taken from ~/.pgpass, which has 600 permissions and look like this:

   localhost:5432:my_db:some_pg_user:H+SCs9;_@D