Обсуждение: Conditionally executing multiple statements in series as single SQL statement

Hi all,

In C, and many other programming languages, statements like the following are popular:

int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());

With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns non-zero,
'do_second_thing'will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed. Etc. 

In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails',
nofurther expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were
successfullyexecuted. 

For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present multiple
statements(such as INSERT statements) and only execute the later ones if the earlier ones have been executed without
error.And I'd like to be able to present all the statements within a single, compound SQL statement to the database. 

Is such a thing possible, using any fancy SQL syntactic tricks?


Nathaniel

On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel@yahoo.co.uk> wrote:
> Hi all,
>
> In C, and many other programming languages, statements like the following are popular:
>
> int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());
>
> With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns
non-zero,'do_second_thing' will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed.
Etc.
>
> In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails',
nofurther expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were
successfullyexecuted. 
>
> For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present
multiplestatements (such as INSERT statements) and only execute the later ones if the earlier ones have been executed
withouterror. And I'd like to be able to present all the statements within a single, compound SQL statement to the
database.
>
> Is such a thing possible, using any fancy SQL syntactic tricks?

No tricks necessary.  What you are describing is called a transaction.

CREATE TABLE testing (
  id integer,
  name text unique
);

BEGIN;
INSERT INTO testing(id,name) values (1,'Bob');
INSERT INTO testing(id,name) values (2,'Joe');
INSERT INTO testing(id,name) values (3,'Sally');
COMMIT;

BEGIN;
INSERT INTO testing(id,name) values (4,'Ann');
-- the next statement will cause an error
-- due to violation of the unique constraint
INSERT INTO testing(id,name) values (5,'Bob');
-- We do a rollback, which will put the database
-- back into the state it was in just before the
-- second BEGIN statement
ROLLBACK;

SELECT * FROM TESTING;

See the documentation and Google about transactions.

Sean

On 18/12/09, Nathaniel Trellice (naptrel@yahoo.co.uk) wrote:
> In other words, later statements will only be executed if all before
> them have 'gone well'. When a statement 'fails', no further
> expressions are executed.. The variable 'status' is non-zero if, and
> only if, all four things were successfully executed.

You could do something along the following lines:

CREATE OR REPLACE FUNCTION
    fn_test ( integer) RETURNS INTEGER
    AS $$
    DECLARE
        input    ALIAS   for $1;
        status   INTEGER := 0;
        returner INTEGER := 0;
    BEGIN
        PERFORM fn_test1 (input);
        IF NOT FOUND THEN
            RETURN returner;
        ELSE
           SELECT INTO status * FROM fn_test2 (input);
           IF status != 1 THEN
                RAISE NOTICE 'status from fn_test2 not expected %' % status
                RETURN returner;
           ELSE
                ...etc...
           END IF;
           returner = 1
           RETURN returner;
        END IF;
END;$$
    LANGUAGE plpgsql;

-
Rory Campbell-Lange
Director
rory@campbell-lange.net

Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928

Sean, I am not sure if
what Nathaniel needs is
really a transaction.

The concept of transaction IIRC has just two possible outcomes, Either
everything is executed or nothing is executed.

But it seems that he needs do_first_thing() and do_second_thing() to be
executed if, e.g. do_third_thing() fails. do_forth_thing() should not be
executed, in this scenario, but the first two actions do.

If we bracket these actions in a transaction nothing would be executed if
any of the actions fail, but I guess Nataniel needs the previous actions to
be executed (and not the next).

Nataniel, am I correctly undestanding the background of your question?

I 'm not realizing if this can be done in pure SQL, but it should be easy to
be done in pqplsql or other procedural language

Best,
Oliveiros


----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Nathaniel Trellice" <naptrel@yahoo.co.uk>
Cc: <pgsql-novice@postgresql.org>
Sent: Friday, December 18, 2009 1:16 PM
Subject: Re: [NOVICE] Conditionally executing multiple statements in series
as single SQL statement


On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel@yahoo.co.uk>
wrote:
> Hi all,
>
> In C, and many other programming languages, statements like the following
> are popular:
>
> int status = (do_first_thing() && do_second_thing() && do_third_thing() &&
> do_fourth_thing());
>
> With this kind of expression, the program calls the function
> 'do_first_thing'. If, and only if, that returns non-zero,
> 'do_second_thing' will be executed. Again, if and only if that returns
> non-zero, 'do_third_thing' is executed. Etc.
>
> In other words, later statements will only be executed if all before them
> have 'gone well'. When a statement 'fails', no further expressions are
> executed.. The variable 'status' is non-zero if, and only if, all four
> things were successfully executed.
>
> For convenience, I'd really like to be able to achieve similar behaviour
> within an SQL statement, i.e. present multiple statements (such as INSERT
> statements) and only execute the later ones if the earlier ones have been
> executed without error. And I'd like to be able to present all the
> statements within a single, compound SQL statement to the database.
>
> Is such a thing possible, using any fancy SQL syntactic tricks?

No tricks necessary.  What you are describing is called a transaction.

CREATE TABLE testing (
  id integer,
  name text unique
);

BEGIN;
INSERT INTO testing(id,name) values (1,'Bob');
INSERT INTO testing(id,name) values (2,'Joe');
INSERT INTO testing(id,name) values (3,'Sally');
COMMIT;

BEGIN;
INSERT INTO testing(id,name) values (4,'Ann');
-- the next statement will cause an error
-- due to violation of the unique constraint
INSERT INTO testing(id,name) values (5,'Bob');
-- We do a rollback, which will put the database
-- back into the state it was in just before the
-- second BEGIN statement
ROLLBACK;

SELECT * FROM TESTING;

See the documentation and Google about transactions.

Sean

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On Fri, Dec 18, 2009 at 8:45 AM, Oliveiros C,
<oliveiros.cristina@marktest.pt> wrote:
> Sean, I am not sure if
> what Nathaniel needs is
> really a transaction.
>
> The concept of transaction IIRC has just two possible outcomes, Either
> everything is executed or nothing is executed.
>
> But it seems that he needs do_first_thing() and do_second_thing() to be
> executed if, e.g. do_third_thing() fails. do_forth_thing() should not be
> executed, in this scenario, but the first two actions do.
>
> If we bracket these actions in a transaction nothing would be executed if
> any of the actions fail, but I guess Nataniel needs the previous actions to
> be executed (and not the next).
>
> Nataniel, am I correctly undestanding the background of your question?
>
> I 'm not realizing if this can be done in pure SQL, but it should be easy to
> be done in pqplsql or other procedural language

Good point--my bad.  SAVEPOINTS might still be helpful, though, if
used in conjunction with the transaction.

Sean


> ----- Original Message -----
> From: "Sean Davis" <sdavis2@mail.nih.gov>
> To: "Nathaniel Trellice" <naptrel@yahoo.co.uk>
> Cc: <pgsql-novice@postgresql.org>
> Sent: Friday, December 18, 2009 1:16 PM
> Subject: Re: [NOVICE] Conditionally executing multiple statements in series
> as single SQL statement
>
>
> On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel@yahoo.co.uk>
> wrote:
>> Hi all,
>>
>> In C, and many other programming languages, statements like the following
>> are popular:
>>
>> int status = (do_first_thing() && do_second_thing() && do_third_thing() &&
>> do_fourth_thing());
>>
>> With this kind of expression, the program calls the function
>> 'do_first_thing'. If, and only if, that returns non-zero,
>> 'do_second_thing' will be executed. Again, if and only if that returns
>> non-zero, 'do_third_thing' is executed. Etc.
>>
>> In other words, later statements will only be executed if all before them
>> have 'gone well'. When a statement 'fails', no further expressions are
>> executed.. The variable 'status' is non-zero if, and only if, all four
>> things were successfully executed.
>>
>> For convenience, I'd really like to be able to achieve similar behaviour
>> within an SQL statement, i.e. present multiple statements (such as INSERT
>> statements) and only execute the later ones if the earlier ones have been
>> executed without error. And I'd like to be able to present all the
>> statements within a single, compound SQL statement to the database.
>>
>> Is such a thing possible, using any fancy SQL syntactic tricks?
>
> No tricks necessary.  What you are describing is called a transaction.
>
> CREATE TABLE testing (
>  id integer,
>  name text unique
> );
>
> BEGIN;
> INSERT INTO testing(id,name) values (1,'Bob');
> INSERT INTO testing(id,name) values (2,'Joe');
> INSERT INTO testing(id,name) values (3,'Sally');
> COMMIT;
>
> BEGIN;
> INSERT INTO testing(id,name) values (4,'Ann');
> -- the next statement will cause an error
> -- due to violation of the unique constraint
> INSERT INTO testing(id,name) values (5,'Bob');
> -- We do a rollback, which will put the database
> -- back into the state it was in just before the
> -- second BEGIN statement
> ROLLBACK;
>
> SELECT * FROM TESTING;
>
> See the documentation and Google about transactions.
>
> Sean
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>

Nathaniel, PostgreSQL does support Perl which is full of tricks. I dislike such tricks because they make the code much harder to read. For that reason even in Perl I sometimes write

if (!defined($a)) then { $a="value"; }

instead just

$a ||= "value";

The latter well known Perl idiom is much shorter and much easier to type but much harder to read, especially for people not proficient in Perl. The same applies to the "&&" trick which will execute the second part only if the first part has executed correctly.

On Fri, 2009-12-18 at 13:01 +0000, Nathaniel Trellice wrote:

Hi all,

In C, and many other programming languages, statements like the following are popular:

int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());

With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns non-zero, 'do_second_thing' will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed. Etc.

In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails', no further expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were successfully executed.

For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present multiple statements (such as INSERT statements) and only execute the later ones if the earlier ones have been executed without error. And I'd like to be able to present all the statements within a single, compound SQL statement to the database.

Is such a thing possible, using any fancy SQL syntactic tricks?


Nathaniel

Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions

Help!

I am running PostGreSQL 8.0 on eComStation (nee OS/2)

I can't start the database (name = oz).

I try to vacuum and get this message:

Vacuuming of database "oz" failed: ERROR: catalog is missing 3
attributes for relid 45564.

Can anybody tell me what this message means and what, if anything I can
do to fix it?

Thanks,

John

>
>
>
>

"John J. Urbaniak" <jjurban@attglobal.net> writes:
> I try to vacuum and get this message:

> Vacuuming of database "oz" failed: ERROR: catalog is missing 3
> attributes for relid 45564.

> Can anybody tell me what this message means and what, if anything I can
> do to fix it?

It means that some of the pg_attribute rows for the table with oid 45564
seem to be missing.  (Try "select relname from pg_class where oid = 45564"
to find out which table that is.)

If you're really lucky, this is just index corruption and "reindex table
pg_attribute" will fix it.  I forget whether 8.0 requires you to do
anything special to reindex pg_attribute --- you might need to do it in
a standalone backend.  Read the REINDEX man page.

If that doesn't fix it, you're pretty much out of luck as far as
recovering that table goes, but you might be able to just drop it
and perhaps the rest of the database will be okay.  (Or perhaps not
... no way to tell from this whether the corruption in pg_attribute
hurt anything else.)  I don't think DROP TABLE will work, but you
could delete the pg_class row for it and then pg_dump should work.

I'd recommend dump, initdb, reload to make sure there's not any
hidden corruption lingering.  This would also be a great opportunity
to update to something less obsolete than PG 8.0.  We don't support
or recommend anything before 8.2 on Windows-ish systems, and even
then you want 8.2.something-pretty-recent.

            regards, tom lane

Tom Lane wrote:

Thanks for the advice.  But I can't even connect to the database, nor start the server on it.  I guess the data is just lost.

I don't know if there is an eCS (or OS/2) version of PGS beyond 8.0

I'll look into it.

Thanks,

John

"John J. Urbaniak" <jjurban@attglobal.net> writes: 

I try to vacuum and get this message:   

Vacuuming of database "oz" failed: ERROR: catalog is missing 3 
attributes for relid 45564.   

Can anybody tell me what this message means and what, if anything I can 
do to fix it?   

It means that some of the pg_attribute rows for the table with oid 45564
seem to be missing.  (Try "select relname from pg_class where oid = 45564"
to find out which table that is.)

If you're really lucky, this is just index corruption and "reindex table
pg_attribute" will fix it.  I forget whether 8.0 requires you to do
anything special to reindex pg_attribute --- you might need to do it in
a standalone backend.  Read the REINDEX man page.

If that doesn't fix it, you're pretty much out of luck as far as
recovering that table goes, but you might be able to just drop it
and perhaps the rest of the database will be okay.  (Or perhaps not
... no way to tell from this whether the corruption in pg_attribute
hurt anything else.)  I don't think DROP TABLE will work, but you
could delete the pg_class row for it and then pg_dump should work.

I'd recommend dump, initdb, reload to make sure there's not any
hidden corruption lingering.  This would also be a great opportunity
to update to something less obsolete than PG 8.0.  We don't support
or recommend anything before 8.2 on Windows-ish systems, and even
then you want 8.2.something-pretty-recent.
		regards, tom lane
 

"John J. Urbaniak" <jjurban@attglobal.net> writes:
> Thanks for the advice.  But I can't even connect to the database, nor
> start the server on it.  I guess the data is just lost.

Hm?  Then how did you get as far as having vacuumdb produce that error?

            regards, tom lane

Tom Lane wrote:

"John J. Urbaniak" <jjurban@attglobal.net> writes: 

Thanks for the advice.  But I can't even connect to the database, nor 
start the server on it.  I guess the data is just lost.   

Hm?  Then how did you get as far as having vacuumdb produce that error?
		regards, tom lane
 

Sorry.  I can start the server, but I can't connect to the database.

John

"John J. Urbaniak" <jjurban@attglobal.net> writes:
>> Hm?  Then how did you get as far as having vacuumdb produce that error?

> Sorry.  I can start the server, but I can't connect to the database.

That doesn't make any sense either, unless you have more corruption than
the previous message indicated.  The error you showed was something that
would only occur on an attempt to access a user table.

Maybe you have something in a .psqlrc file that is doing something extra
when you try to connect?

            regards, tom lane

Tom Lane wrote:

"John J. Urbaniak" <jjurban@attglobal.net> writes: 

Hm?  Then how did you get as far as having vacuumdb produce that error?     

Sorry.  I can start the server, but I can't connect to the database.   

That doesn't make any sense either, unless you have more corruption than
the previous message indicated.  The error you showed was something that
would only occur on an attempt to access a user table.

Maybe you have something in a .psqlrc file that is doing something extra
when you try to connect?
		regards, tom lane
 

I don't know.  It's strange.  I "start" the database with E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"

I get no error message on the call.

But I notice that there is no postmaster.id file generated.

John

"John J. Urbaniak" <jjurban@attglobal.net> writes:
> I don't know.  It's strange.  I "start" the database with
> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"

> I get no error message on the call.

> But I notice that there is no postmaster.id file generated.

What shows up in the logfile?

            regards, tom lane

Tom Lane wrote:

"John J. Urbaniak" <jjurban@attglobal.net> writes: 

I don't know.  It's strange.  I "start" the database with 
E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"   

I get no error message on the call.   

But I notice that there is no postmaster.id file generated.   

What shows up in the logfile?
		regards, tom lane
 

I don't know where the logfile is.

John

On Thu, Jan 7, 2010 at 14:11, John J. Urbaniak <jjurban@attglobal.net> wrote:

>> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"
[...]
> I don't know where the logfile is.

The "-l logfile" tells PG to save the logfile as "logfile" in the
current directory where the command was issued. A good idea is to put
an absolute path to the logfile; "-l E:\postgres_logfile" or similiar.


--
- Rikard

"John J. Urbaniak" <jjurban@attglobal.net> writes:
>>> I don't know.  It's strange.  I "start" the database with
>>> E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start"
>>
>> What shows up in the logfile?

> I don't know where the logfile is.

You said "-l logfile".  It ought to be named logfile, in the directory
you did this in.

            regards, tom lane