Re: Begin / End blocks

On Tue, 14 May 2002, Johnson, Shaunn wrote:

> Howdy:
>
> Running Postgres 7.1.3 on RedHat Linux 2.4.7-rel 10.
>
> I'm trying to learn how to use the BEGIN / END
> blocks.  My goal is to test the count
> of a table and do such and such from there.
>
> [pseudo code]
>
> begin
>     if
>         select sum (*) from table < 1500
>     then
>         select into new_table * from table
>     else
>         return ''f'';
>     end if;
> end

The above code is basically "pseudo code" and gets across your desires
perfectly.

No, you don't HAVE to use a function, you could do this externally with
almost any programming language that can connect to a postgresql or odbc
database, like PERL, PHP, C, Python, Tcl/Tk and many others.

You could also use one of the built in scripting languages postgresql
supports, like pgplsql to do it in a stored procedure.  That's what
functions in postgresql are called by the way.

Begin / End blocks are what allow you to perform "all or nothing" type
operations called transactions.

the idea behind transactions is that if one part of a transaction fails,
the whole transaction fails and all changes to your data are rolled back
to their original state.  The classic example is debiting your checking
account to pay a bill.  You don't want the money to disappear from your
account while NOT appearing on your credit for the bill it was intended
for, so if the payment doesn't go through, the debit is automatically
rolled back and your account unaffected.