Re: psql scripting tutorials

On Tue, 9 Sep 2008, Artacus wrote:

> Can psql access environmental variables or command line params?

$ cat test.sql
select :TEST as "input";
$ psql -v TEST=16 -f test.sql
  input
-------
     16
(1 row)

You can find out more about what you can do with variable substitution at
http://www.postgresql.org/docs/8.3/static/app-psql.html There are some
reserved IDs, some of which can be useful in return for the fact that you
have to avoid their names.

Another handy trick in this area is to just have your shell script write a
small file with \set command or similar generated code containing
parameters or setup stuff, and then have a larger main script
include that with \i

As for environment variables, if it's just things like COPY you want to
automate this works:

$ F="/x/y"
$ psql -c "COPY x from '$F'"

Other approaches:

1) Use the untrusted PL/PerlU to just handle the whole operation
2) Write something in a more mainstream programming language that you can
hook into the database.
3) Use PL/sh to call your scripts instead to generate what you need:
http://plsh.projects.postgresql.org/

If the main goal is to automate COPY, though, those will probably just
slow you down.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD