Thread: psql scripting tutorials
I'd like to learn a little more about writing psql scripts does anyone know of any resources outside of the manual?
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 -- > > Kevin >
-------------- 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)