pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Where is the problem ?
2+ messages / 2 participants
[nested] [flat]

* Where is the problem ?
@ 1998-06-01 12:29  Piotr Adamiak <apj@vr.pl>
  0 siblings, 0 replies; 2+ messages in thread

From: Piotr Adamiak @ 1998-06-01 12:29 UTC (permalink / raw)
  To: pgsql-sql

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;





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* RE: [SQL] Where is the problem ?
@ 1998-06-01 21:05  Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Jackson, DeJuan @ 1998-06-01 21:05 UTC (permalink / raw)
  To: Piotr Adamiak <apj@vr.pl>; pgsql-sql

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;
> 
> 



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1998-06-01 21:05 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-01 12:29 Where is the problem ? Piotr Adamiak <apj@vr.pl>
1998-06-01 21:05 RE: [SQL] Where is the problem ? Jackson, DeJuan <djackson@cpsgroup.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox