Обсуждение: Begin / End blocks

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

[/code]

questions:

* Do I have to make this a function? 

* Is there a way to assign the value of
the count (or, sum of records in table)
to a variable and test for -that- ?

* I'm looking at the postgresql.org site
for examples, but all of the BEGIN / END
blocks are within creating a function - I'm
not sure I want to create one ...

* I'm sure the code above is wrong
(especially with the if statement) -
where can I find better code (groups.
google.com isn't working well, either).

Thanks!

-X

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

[/code]

questions:

* Do I have to make this a function? 

* Is there a way to assign the value of
the count (or, sum of records in table)
to a variable and test for -that- ?

* I'm looking at the postgresql.org site
for examples, but all of the BEGIN / END
blocks are within creating a function - I'm
not sure I want to create one ...

* I'm sure the code above is wrong
(especially with the if statement) -
where can I find better code (groups.
google.com isn't working well, either).

 

BEGIN/END can refer to beginning and ending transactions (for safety, speed, etc.)

This is different from BEGIN/END as used in function. What you want is to create a function. Look in the programmer's manual under procedural languages for help using plpgsql.

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.

You can make it a function but you can also do

BEGIN;

SELECT CASE WHEN (SELECT SUM(*) FROM table) < 1500
                 THEN (INSERT INTO new_table (SELECT * FROM table))
            ELSE ''f'' END AS test;
END;

I have not tested it but it should work.

HTH

Darren Ferguson

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
>
> [/code]
>
> questions:
>
> * Do I have to make this a function?
>
> * Is there a way to assign the value of
> the count (or, sum of records in table)
> to a variable and test for -that- ?
>
> * I'm looking at the postgresql.org site
> for examples, but all of the BEGIN / END
> blocks are within creating a function - I'm
> not sure I want to create one ...
>
> * I'm sure the code above is wrong
> (especially with the if statement) -
> where can I find better code (groups.
> google.com isn't working well, either).
>
> Thanks!
>
> -X
>