pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhere 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