Thread

RE: [SQL] Where is the problem ?

Jackson, DeJuan <djackson@cpsgroup.com> — 1998-06-01T21:05:33Z

Works for me...

test=> select logz('abc','mswindows',2);
logz
----
   3
(1 row)

test=> select * from ipl_use;
ln|user|prog     |ad|     wdate|wtime   
--+----+---------+--+----------+--------
 3|abc |mswindows| 2|06-01-1998|15:53:58
(1 row)

test=> 

Begin/End are used to tie a unit of work together.  Such as a bank
transfer.  You take money from one account and put it in another. You
want it to happen all at once or not at all (in the case of a overdraft
or lightning strike).
So:
 BEGIN
 LOCK TABLE accounts; -- Just to be safe
 UPDATE accounts
    SET balance = balance - 7500.00
  WHERE acc_num = '653765345';
 UPDATE accounts
    SET balance = balance + 7500.00
  WHERE acc_num = '098439853';
 END
Now assuming that table accounts has a constraint that doesn't allow
balance to go below zero and there is only 6000 in the first account the
transfer wont happen.  This is a very simplistic example but I'm sure
you get the idea.
		Hope this helps,
			-DEJ


> Hi!
> 
> I am a beginning postgres user and having a more or less free weekend
> I
> decided to do a test application using sql. Well all is not so great
> because when I run "select logz('abc','mswindows',2)\g followed by a
> select * from ipl_use\g I get a core dump :((( Does anybody know whats
> wrong ? Also How do you use the transactions (begin/end) correctly ?
> Also 2: Is there a way to get Insert to insert only when a condition
> is
> met such as when there is no such user ?
> 
> BTW: I am running postgres 6.2.1 on a RedHat 5.* system.
> 
> Best regards,
> 
> /apj
> 
> -----------------------------[CUT]------------------------------------
> 
> CREATE TABLE ipl_ads (
> ln int4,
> ad varchar(60),
> owner varchar(60),
> fromwhen date,
> counter int4
> );
> 
> CREATE TABLE ipl_use (
> ln int4,
> user varchar(128),
> prog varchar(128),
> ad int4,
> wdate date,
> wtime time
> );
> 
> CREATE SEQUENCE ad_ln;
> CREATE SEQUENCE use_ln;
> 
> create table ad_num (f1 int4);
> insert into ad_num values (1);
> 
> create table licz (f2 int4);
> insert into licz values (1);
> 
> create function licznik() returns int4 as
> 'begin;
> update licz set f2 = f2 + 1;
> end;
> select f2 from licz; '
> language 'sql';
> 
> create function which_ad(int4) returns int4 as
> 'begin;
> update ad_num set f1 = f1 + 1 where ad_num.f1 <= $1;
> update ad_num set f1 = 1 where ad_num.f1 > $1;
> update ipl_ads set counter = counter + 1 where ipl_ads.ln = ad_num.f1;
> end;
> select ln from ipl_ads where ipl_ads.ln = ad_num.f1; '
> language 'sql';
> 
> create function logz(varchar, varchar, int4) returns int4 as
> 'insert into ipl_use values (licznik(), $1, $2, $3, current_date,
> current_time);
> select ln from ipl_use where ipl_use.ln = licz.f2; '
> language 'sql';
> 
> update licz set f2 = 2;
> 
>