Thread: Conditionnal validation for transaction

Hy all of you,

1 - Is there a way to have conditions for committing transactions like in oracle : http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-transaction-62

2 - Is there a way to declare local variables for a SQL statement without beiing in a function?

regards

2.  The short answer is No.  I've got the same issue.  I come from a
different sql that had a CREATE VARAIBLE which was good for the session.
 With PostGres, I've created a sessionsettings table and a bunch of
functions to get by variable and use the value.  My perceived downside
is that this causes a lot of calls to be made to get the data instead of
setting them one time (for most items).  I've been told that the table
will probably be cached so it will cost very little.

Michael Gould
Intermodal Software Solutions, LLC
904-226-0978



-------- Original Message --------
Subject: [GENERAL] Conditionnal validation for transaction
From: Florent THOMAS <mailinglist@tdeo.fr>
Date: Mon, March 19, 2012 8:28 am
To: pgsql-general@postgresql.org

 Hy all of you,

 1 - Is there a way to have conditions for committing transactions like
in oracle :
http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-transaction-62

 2 - Is there a way to declare local variables for a SQL statement
without beiing in a function?

 regards

On Mon, Mar 19, 2012 at 3:28 PM, Florent THOMAS <mailinglist@tdeo.fr> wrote:
> Hy all of you,
>
> 1 - Is there a way to have conditions for committing transactions like in
> oracle :
> http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-transaction-62
>
> 2 - Is there a way to declare local variables for a SQL statement without
> beiing in a function?

PostgreSQL follows the SQL standard which does not allow anything like that.

Later versions do allow anonymous blocks, also known as DO statements
that allow you to execute some code to allow decision making like
that. So the Oracle example is very similar code in PostgreSQL, except
that you can't issue ROLLBACK and COMMIT. But then you don't need to
because you can do a conditional error or drop through to a commit.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Le 19/03/2012 16:57, Simon Riggs a écrit :

On Mon, Mar 19, 2012 at 3:28 PM, Florent THOMAS <mailinglist@tdeo.fr> wrote:

Hy all of you,

1 - Is there a way to have conditions for committing transactions like in
oracle :
http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-transaction-62

2 - Is there a way to declare local variables for a SQL statement without
beiing in a function?

PostgreSQL follows the SQL standard which does not allow anything like that.

Later versions do allow anonymous blocks, also known as DO statements
that allow you to execute some code to allow decision making like
that. So the Oracle example is very similar code in PostgreSQL, except
that you can't issue ROLLBACK and COMMIT.

Thanks, Could you precise the sentence bellow

 But then you don't need to
because you can do a conditional error or drop through to a commit.

How do you do that?

Regards

Florent THOMAS wrote:
>>> 1 - Is there a way to have conditions for committing transactions
like in oracle :
>>>
http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-trans
action-62
>>
>> PostgreSQL follows the SQL standard which does not allow anything
like that.
>>
>> Later versions do allow anonymous blocks, also known as DO statements
>> that allow you to execute some code to allow decision making like
>> that. So the Oracle example is very similar code in PostgreSQL,
except
>> that you can't issue ROLLBACK and COMMIT.
>
> Thanks, Could you precise the sentence bellow
>
>
>> But then you don't need to
>> because you can do a conditional error or drop through to a commit.
>
> How do you do that?

I don't know what exactly Simon meant here, but I'd do it like that
in PostgreSQL (example from your link):

CREATE TABLE transtest(x smallint);

INSERT INTO transtest VALUES (1), (2);

CREATE FUNCTION dec_trans() RETURNS void LANGUAGE plpgsql AS
$$DECLARE
  minx transtest.x%TYPE;
BEGIN
  UPDATE transtest SET x=x-1;
  SELECT min(x) INTO minx FROM transtest;
  IF minx<0 THEN
    RAISE EXCEPTION 'bad decrement';
  END IF;
END$$;

SELECT * FROM transtest;
 x
---
 1
 2
(2 rows)

DO LANGUAGE plpgsql
$$BEGIN
  PERFORM dec_trans();
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END$$;

SELECT * FROM transtest;
 x
---
 0
 1
(2 rows)

DO LANGUAGE plpgsql
$$BEGIN
  PERFORM dec_trans();
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END$$;

SELECT * FROM transtest;
 x
---
 0
 1
(2 rows)


Yours,
Laurenz Albe