bummer, I need to double quote all my existing db-ops scripts to account for the extra 'su -c ...' and just now I found that 'create database' does not support 'IF NOT EXIST' and that I have to go in a huge roundabout involving some creature called \gexec which after an hour I found out it is not supported in 'psql -c ...'. Hey I think I have found in PG a new sadomasochistic group. Whips! yummy not.

Edit: psql -Upostgres will run the commands via the 'postgres' linux user. I thought it runs the SQL commands via a db user. OK now I can avoid the constant su but I have to enter password all the time! So better running the script via su -c db-init-script.sh - postgres.

OK, it seems I had some progress sanEtising my interactions with postgres and psql, its sql shell.

Once I create a database and a database user I no longer need to use the "postgres" linux account to run commands via su thanks to the "service file". This is a simple configuration file which holds dbname, dbuser, pass etc.:

# located at x/y/z.config
[myservice]
host=
port=
# above 2 can be left empty to denote unix socket connection
dbname=...
user=...
password=...
[download]

And then PGSERVICEFILE=x/y/z.config psql 'service=myservice'

I have also found that psql accepts a connection string like: sql 'postgresql://mydbuser:mypassword@host:port/mydbname' (host, port are optional, leave empty for unix socket connection)

It's also possible to run Perl scripts within the database (using the extension 'plperl' or 'plperlu'. This is quite useful for performance optimization, writing complex triggers and such. But it's also quite nice for debugging and administration, if you can run certain scripts just by selecting from them. A basic data generator could look like this:

-- Create the extension
CREATE EXTENSION plperlu;

-- define how the table row returned by the perl function looks like
CREATE TYPE poemrow AS (
    poemrowid bigint,
    verserowid bigint,
    verseline text
);

-- Automatic poem generator
CREATE OR REPLACE FUNCTION bottlesofbeer(bigint) RETURNS SETOF poemrow
+ AS $$
    my ($bottlecount) = @_;

    if($bottlecount < 0) {
        $bottlecount = 0;
    };

    my $rowid = 0;

    my @verselines = (
        'COUNT bottles of beer on the wall, COUNT bottles of beer.',
        'Take one down, pass it around!',
        'REDUCED bottles of beer on the wall',
        ''
    );

    my $vcount;
    while($bottlecount) {
        my $nextcount = $bottlecount - 1;

        $vcount = 1;
        foreach my $rawline (@verselines) {
            my $line = '' . $rawline; # COPY the line
            $line =~ s/COUNT/$bottlecount/g;
            $line =~ s/REDUCED/$nextcount/g;

            return_next({poemrowid => $rowid, verserowid => $vcount, v
+erseline => $line });

            $vcount++;
            $rowid++;
        }

        $bottlecount--;
    }

    return_next({poemrowid => $rowid, verserowid => $vcount, verseline
+ => 'No more beer, buy some more!' });
            
    return undef; # We are done here
$$ LANGUAGE plperlu;
[download]

Now you can use the function like a read-only table:

ER_DEV_DB=# SELECT * FROM bottlesofbeer(3);
 poemrowid | verserowid |                     verseline               
+      
-----------+------------+---------------------------------------------
+------
         0 |          1 | 3 bottles of beer on the wall, 3 bottles of 
+beer.
         1 |          2 | Take one down, pass it around!
         2 |          3 | 2 bottles of beer on the wall
         3 |          4 | 
         4 |          1 | 2 bottles of beer on the wall, 2 bottles of 
+beer.
         5 |          2 | Take one down, pass it around!
         6 |          3 | 1 bottles of beer on the wall
         7 |          4 | 
         8 |          1 | 1 bottles of beer on the wall, 1 bottles of 
+beer.
         9 |          2 | Take one down, pass it around!
        10 |          3 | 0 bottles of beer on the wall
        11 |          4 | 
        12 |          5 | No more beer, buy some more!
(13 rows)

ER_DEV_DB=# SELECT verseline FROM bottlesofbeer(8) WHERE verserowid = 
+3 ORDER BY poemrowid DESC;
           verseline           
-------------------------------
 0 bottles of beer on the wall
 1 bottles of beer on the wall
 2 bottles of beer on the wall
 3 bottles of beer on the wall
 4 bottles of beer on the wall
 5 bottles of beer on the wall
 6 bottles of beer on the wall
 7 bottles of beer on the wall
(8 rows)
[download]

Of course, plperl can also run SQL statements, access results and more, including to be used as a INSERT/UPDATE/DELETE trigger function.

A simple example would be to make sure every customer entry has a customer number, avoiding the pitfalls of database sequences (which may leave big holes for cancelled transactions etc), something like this:

-- Create a perl trigger function in database schema "pos"
CREATE OR REPLACE FUNCTION pos.customersinserttrigger() RETURNS trigge
+r AS $$
    use strict;

    if(defined($_TD->{new}->{'customer_number'}) && $_TD->{new}->{'cus
+tomer_number'} ne '') {
        return;
    }

    my $selsth = spi_prepare("SELECT max(customer_number) AS max_custo
+mer_number FROM pos.customers WHERE customer_number IS NOT NULL");
    my $nextnum = 1_000_000;

    my @lines;
    {
        my $retval = spi_exec_prepared($selsth);
        if($retval->{status} ne 'SPI_OK_SELECT') {
            elog(ERROR, "Failed to select all rows in pos.customers");
        }

        if($retval->{processed}) {
            @lines = @{$retval->{rows}};
        }
        spi_freeplan $selsth;
    }

    foreach my $line (@lines) {
        my $tmp = $line->{max_customer_number};
        $tmp = 0 + $tmp;
        if($tmp > $nextnum) {
            $nextnum = $tmp;
        }
    }
    $nextnum++;

    $_TD->{new}->{'customer_number'} = $nextnum;

    return "MODIFY"; # EXECUTE, all updates sucessfull

$$ LANGUAGE plperlu;

-- Add a trigger to table "customers" in schema "pos" to call our trig
+ger function on every inserted or updated row
CREATE TRIGGER insertupdate_customers_trigger
    BEFORE INSERT OR UPDATE ON pos.customers
    FOR EACH ROW EXECUTE FUNCTION pos.customersinserttrigger();
[download]

PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP
Also check out my sisters artwork and my weekly webcomics

Hey I think I have found in PG a new sadomasochistic group. Whips! yummy not.

Haha! True, but once you are initiated, you'll be hooked. I know I was when this girlfriend of mine introduced me to bonding, but I digress.

Postgresql's documentation is outstanding, and very easy to search. I suggest you invest some time to get the hang of it, by reading Chapter I Tutorial and investigating Chapter VI Reference, which will cover most your needs.

The pgsql-general list is very active, you'll get all the help you need there, providing you did basic research. I've been reading it every day for twenty years, I learn something new every time.

Marica : Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance

thanks, I will definetely persist with Pg