Thread: Conditionally executing multiple statements in series as single SQL statement
Conditionally executing multiple statements in series as single SQL statement
From
Nathaniel Trellice
Date:
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
Re: Conditionally executing multiple statements in series as single SQL statement
From
Sean Davis
Date:
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
Re: Conditionally executing multiple statements in series as single SQL statement
From
Rory Campbell-Lange
Date:
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
Re: Conditionally executing multiple statements in series as single SQL statement
From
"Oliveiros C,"
Date:
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
Re: Conditionally executing multiple statements in series as single SQL statement
From
Sean Davis
Date:
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 > >
Re: Conditionally executing multiple statements in series as single SQL statement
From
Mladen Gogala
Date:
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:
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
| |||||
Attachment
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
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
Sorry. I can start the server, but I can't connect to the database."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
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:
I get no error message on the call.
But I notice that there is no postmaster.id file generated.
John
I don't know. It's strange. I "start" the database with E:\PGSQL\BIN\PG_CTL.EXE "-D E:/PGSData -l logfile start""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 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
I don't know where the logfile is."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
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