psql scripting tutorials

Artacus wrote:

I'd like to learn a little more about writing psql scripts does anyone
know of any resources outside of the manual?

Ok then. Does anyone have any tips or best practices for scripting psql?

I'll probably write some bash scripts to pull csv files over then script
psql to do a COPY into an import schema then run a pl/pgsql procedure,
er function, to do all ETL.

Who else is doing something like this? Can psql access environmental
variables or command line params? Or do I have to have my bash script
write a psql script every time?

Thanks

On Sep 10, 2008, at 2:46 AM, Artacus wrote:

Who else is doing something like this? Can psql access environmental
variables or command line params? Or do I have to have my bash
script write a psql script every time?

The psql \! command can execute shell commands. You can also use ``,
e.g.

=> \echo `date`
Wed Sep 10 08:07:19 EDT 2008

John DeSoi, Ph.D.

On Wed, Sep 10, 2008 at 12:46 AM, Artacus <artacus@comcast.net> wrote:

Artacus wrote:

I'd like to learn a little more about writing psql scripts does anyone
know of any resources outside of the manual?

Ok then. Does anyone have any tips or best practices for scripting psql?

I'll probably write some bash scripts to pull csv files over then script
psql to do a COPY into an import schema then run a pl/pgsql procedure, er
function, to do all ETL.

This sounds a lot like what I did in my last job using bash for most
things, and php for the more complicated stuff. Wrote a simple oracle
to pgsql table replicator in php that worked pretty well.

This sounds a lot like what I did in my last job using bash for most
things, and php for the more complicated stuff. Wrote a simple oracle
to pgsql table replicator in php that worked pretty well.

Well we do this stuff all the time with Oracle and sql*plus. And I've
heard people hear say about how much better psql is than sql*plus. So I
figured surely someone would have figured that its a really slick way of
automating imports and scheduling with cron... and then blogged about
it. But it looks like I'm on my own.

On Wed, Sep 10, 2008 at 10:14 PM, Artacus <artacus@comcast.net> wrote:

This sounds a lot like what I did in my last job using bash for most
things, and php for the more complicated stuff. Wrote a simple oracle
to pgsql table replicator in php that worked pretty well.

Well we do this stuff all the time with Oracle and sql*plus. And I've heard
people hear say about how much better psql is than sql*plus. So I figured
surely someone would have figured that its a really slick way of automating
imports and scheduling with cron... and then blogged about it. But it looks
like I'm on my own.

Well, sadly I didn't keep a copy of the scripts when I left. I did
something in bash that was basically to run a query, and process each
line as it came out of psql to detect system problems. I had a stats
db with all the events the app generated aggregated by total time /
req / minute and stuffed into the db. 5 to 10 app servers making 60
to 200 inserts a minute each. Not a lot of data each minute, but it
added up. then I had a table with each request type and a max average
and max absolute threshold that we ran a single query to find which
rows were over their maxes and generated alerts. I used the line by
line reading techniques you can find from googling, then used read (I
think it was read) to split the line up into parts to stuff into vars
and do math. I need to make something like that again anyway, I'll
post it when it works.

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

In article <Pine.GSO.4.64.0809110111250.244@westnet.com>,
Greg Smith <gsmith@gregsmith.com> writes:

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)

Nice trick, but when I try the following variant:

psql -v TEST=16 -c 'select :TEST as "input"'

I get

ERROR: syntax error at or near ":"
LINE 1: select :TEST as "input"

This seems to be contrary to the psql manual page:

These assignments are done during a very early stage of start-up...

Harald Fuchs escribi�:

Nice trick, but when I try the following variant:

psql -v TEST=16 -c 'select :TEST as "input"'

I get

ERROR: syntax error at or near ":"
LINE 1: select :TEST as "input"

This seems to be contrary to the psql manual page:

These assignments are done during a very early stage of start-up...

Seems like a bug in -c ...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Harald Fuchs wrote:

In article <Pine.GSO.4.64.0809110111250.244@westnet.com>,
Greg Smith <gsmith@gregsmith.com> writes:

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)

Nice trick, but when I try the following variant:

psql -v TEST=16 -c 'select :TEST as "input"'

I get

ERROR: syntax error at or near ":"
LINE 1: select :TEST as "input"

This seems to be contrary to the psql manual page:

These assignments are done during a very early stage of start-up...

psql manual page:

-c command

--command command
Specifies that psql is to execute one command string, command,
and then exit. This is useful in shell scripts.

command must be either a command string that is completely
parsable by the server (i.e., it contains no psql specific
features), or a single backslash command. Thus you cannot mix
SQL and psql meta-commands with this option.

Peter Eisentraut escribi�:

psql manual page:

-c command

--command command
Specifies that psql is to execute one command string, command,
and then exit. This is useful in shell scripts.

command must be either a command string that is completely
parsable by the server (i.e., it contains no psql specific
features), or a single backslash command. Thus you cannot mix
SQL and psql meta-commands with this option.

Doesn't say about variable expansion ... And it seems to be in a
different realm, because the point is that the command is going to have
a single destination (either \-processing or sending it to the server).

Is psql being just lazy here and avoiding parsing the command?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

At 7:13am -0400 on Thu, 11 Sep 2008, Harald Fuchs wrote:

Nice trick, but when I try the following variant:

psql -v TEST=16 -c 'select :TEST as "input"'

I get [a syntax error]

This seems to be contrary to the psql manual page:

Nope. Take a look at the -c option. Specifically "Thus you cannot mix
SQL and psql meta-commands with this option."

You might try shell interpretation:

$ TEST=16; psql -c "select $TEST as \"input1\";"
$ TEST=16; echo "select $TEST as \"input1\";" | psql

Kevin

Kevin Hunter wrote:

At 7:13am -0400 on Thu, 11 Sep 2008, Harald Fuchs wrote:

Nice trick, but when I try the following variant:

psql -v TEST=16 -c 'select :TEST as "input"'

I get [a syntax error]

This seems to be contrary to the psql manual page:

Nope. Take a look at the -c option. Specifically "Thus you cannot mix
SQL and psql meta-commands with this option."

You might try shell interpretation:

$ TEST=16; psql -c "select $TEST as \"input1\";"
$ TEST=16; echo "select $TEST as \"input1\";" | psql

Whatever happened to pgbash? I see the last update was Feb 2003 but
that was for Pg v7.3.

Rod
--

-------------- Original message ----------------------
From: "Roderick A. Anderson" <raanders@acm.org>

Kevin Hunter wrote:

At 7:13am -0400 on Thu, 11 Sep 2008, Harald Fuchs wrote:

Nice trick, but when I try the following variant:

psql -v TEST=16 -c 'select :TEST as "input"'

I get [a syntax error]

This seems to be contrary to the psql manual page:

Nope. Take a look at the -c option. Specifically "Thus you cannot mix
SQL and psql meta-commands with this option."

You might try shell interpretation:

$ TEST=16; psql -c "select $TEST as \"input1\";"
$ TEST=16; echo "select $TEST as \"input1\";" | psql

Whatever happened to pgbash? I see the last update was Feb 2003 but
that was for Pg v7.3.

Rod
--

Kevin

See also:
http://www.edlsystems.com/shellsql/
http://pgfoundry.org/projects/plsh/
--
Adrian Klaver
aklaver@comcast.net

$ TEST=16; psql -c "select $TEST as \"input1\";"
$ TEST=16; echo "select $TEST as \"input1\";" | psql

Yep that works. My coworker also suggested using <<EOF to simulate a
psql script.

Alvaro Herrera wrote:

Doesn't say about variable expansion ... And it seems to be in a
different realm, because the point is that the command is going to have
a single destination (either \-processing or sending it to the server).

Is psql being just lazy here and avoiding parsing the command?

The intent is to leave open an avenue to pass a command to the server
without any interference from psql at all. I have never been very
comfortable with overloading -c for that purpose, and it certainly
confuses users from time to time. But that's the idea anyway.

Ok then. Does anyone have any tips or best practices for scripting psql?

I'll probably write some bash scripts to pull csv files over then script
psql to do a COPY into an import schema then run a pl/pgsql procedure, er
function, to do all ETL.

Who else is doing something like this? Can psql access environmental
variables or command line params? Or do I have to have my bash script write
a psql script every time?

Maybe you should try pltcl/pltclu - it's very powerfull (and my
favorite) language for ETL within PostgreSQL server (read files, TCP,
regular expresion etc.). If you have XWindow based boxes you may use
tk package and even to use graphical user interface (for example
DialogBox as parameters input) on remotex boxes from pltclu (sometimes
I do that).

For external ETL I am using Java Eclipse or Eclipse RCP Framework and
embeded python language (formaly jython) - very important fact is that
jython scripts may controlled Eclipse widgets (for example
ProgressBar).

Regards,
Blazej

Am 2008-09-11 10:03:03, schrieb Roderick A. Anderson:

Whatever happened to pgbash? I see the last update was Feb 2003 but
that was for Pg v7.3.

I have tried it soe times ago with 7.4 but goten to many errors...
...and gaved up.

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant

-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
+49/177/9351947    50, rue de Soultz         MSN LinuxMichi
+33/6/61925193     67100 Strasbourg/France   IRC #Debian (irc.icq.com)