[SQL] Explicitly rolling back transaction from within a C-Language function

[Mark Dilger]

Hello,

I have a transactional system built on top of
BerkeleyDB which I would like to call from within
Postgres through a C-Language function.  The behavior
of the function will be read-only.  Even so, the
BerkeleyDB-based system's transaction will sometimes
fail.  What I would like to do is propogate that
transactional failure into the Postgres transaction
that called the C-Language function.  Is this
possible?

To clarify what I think I want:  I would like the
C-Language function to return an error condition to
Postgres that will cause the current Postgres
transaction to abort.  I do not know how to do this.

I am not particularly worried about needing to
rollback the BerkeleyDB transaction when the Postgres
transaction fails, because the BerkeleyDB transaction
was read-only anyway, and there are no updates to roll
back.  However, I might want to do this in the future
so information on this subject is also welcome.

Thank you for any help,

mark



__ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Sum() rows

[Mark Dilger]

[EMAIL PROTECTED] wrote:

Hi.
How can I sum a row and show the sum for each row???
For example, in a finances table that have the total movimentation(debit/credit)
in the bank.

i.e:
CREATE TABLE TB1 (id integer primary key, value numeric);
insert into tb1 values (1,20);
insert into tb1 values (2,2);
insert into tb1 values (3,3);
insert into tb1 values (4,17);
insert into tb1 values (5,-0.5);
insert into tb1 values (6,3);

I want a query that returns:
-id- | --- value --- | --- subtot ---
   1 |20.00  | 20.00
   2 | 2.00  | 22.00
   3 | 3.00  | 25.00
   4 |17.00  | 42.00
   5 |-0.50  | 41.50
   6 | 3.00  | 44.50

The subtot colum will be the "prev. subtot colum"+"value colum". :-/
I dont know how to make the "subtot" colum, I tried to use the sum() function
but it not works correctly.
Any idea???

Thanks.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq



CREATE TABLE tb1 (id integer primary key, value numeric);

CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric);

CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$
DECLARE
  tbrowRECORD;
  sbrowsubtotal_type;
BEGIN
  sbrow.subtotal := 0;
  FOR tbrow IN
SELECT id, value FROM tb1 ORDER BY id
  LOOP
sbrow.id := tbrow.id;
sbrow.value := tbrow.value;
sbrow.subtotal := sbrow.subtotal + tbrow.value;
RETURN NEXT sbrow;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;


insert into tb1 (id, value) values (1, 20.0);
insert into tb1 (id, value) values (2, 2.0);
insert into tb1 (id, value) values (3, 3.0);

select * from subtotal();

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Sum() rows

[Mark Dilger]

Mark Dilger wrote:

[EMAIL PROTECTED] wrote:


Hi.
How can I sum a row and show the sum for each row???
For example, in a finances table that have the total 
movimentation(debit/credit)

in the bank.

i.e:
CREATE TABLE TB1 (id integer primary key, value numeric);
insert into tb1 values (1,20);
insert into tb1 values (2,2);
insert into tb1 values (3,3);
insert into tb1 values (4,17);
insert into tb1 values (5,-0.5);
insert into tb1 values (6,3);

I want a query that returns:
-id- | --- value --- | --- subtot ---
   1 |20.00  | 20.00
   2 | 2.00  | 22.00
   3 | 3.00  | 25.00
   4 |17.00  | 42.00
   5 |-0.50  | 41.50
   6 | 3.00  | 44.50

The subtot colum will be the "prev. subtot colum"+"value colum". :-/
I dont know how to make the "subtot" colum, I tried to use the sum() 
function

but it not works correctly.
Any idea???

Thanks.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq




CREATE TABLE tb1 (id integer primary key, value numeric);

CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric);

CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$
DECLARE
  tbrowRECORD;
  sbrowsubtotal_type;
BEGIN
  sbrow.subtotal := 0;
  FOR tbrow IN
SELECT id, value FROM tb1 ORDER BY id
  LOOP
sbrow.id := tbrow.id;
sbrow.value := tbrow.value;
sbrow.subtotal := sbrow.subtotal + tbrow.value;
RETURN NEXT sbrow;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;


insert into tb1 (id, value) values (1, 20.0);
insert into tb1 (id, value) values (2, 2.0);
insert into tb1 (id, value) values (3, 3.0);

select * from subtotal();

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


I forgot to mention that you must run 'createlang plpgsql' on the 
database for this to work, if the language named plpgsql has not already 
been created.  (This command would typically be run from a shell, such 
as sh, bash, etc., and not from within psql.)


Please review 
http://www.postgresql.org/docs/8.0/interactive/app-createlang.html for 
more information about this command.


Cheers!

---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org