Re: [SQL] SQL INSERT/TRIGGER Help
Aww man thank you so much! It worked like a charm! Have a smashing day :D
On Dec 10, 2007 9:43 AM, A. Kretschmer <[EMAIL PROTECTED]>
wrote:
> am Mon, dem 10.12.2007, um 9:27:58 +0200 mailte Poovendran Moodley
> folgendes:
> > I'm not really sure how to the currval() method. I've read up on it and
> I
> > noticed it works with nextval() and setval(). The parameter for
> currval() is a
> > regex - is there a regex to represent the most recently automatically
> generated
> > number ( i.e. a serial field)? If there isn't, I was thinking that a
> trigger
> > could be used so that when an INSERT is executed against the
> Observation_Value
>
>
> Okay, i explain this a little bit more:
>
> first, i create two tables, master and slave. master contains a
> serial-field, this field is referenced by the slave table:
>
> test=# create table master (id serial primary key, name text);
> NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for
> serial column "master.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "master_pkey" for table "master"
> CREATE TABLE
> test=*# create table slave(master_id int references master, val text);
> CREATE TABLE
>
>
> Now i have 2 tables and a sequence named 'master_id_seq', and now the
> insert's:
>
>
> test=*# insert into master (name) values ('test');
> INSERT 0 1
> test=*# insert into slave (master_id, val) values
> (currval('master_id_seq'), 'value');
> INSERT 0 1
>
>
>
> The parameter for currval() is the name of the sequence. Note: you have to
> insert in the master table first, this calls the nextval() for the
> sequence. After, within this database session, you can use currval() to
> obtain the actual value for this sequence. And yes, this way is safe
> also for concurrency.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
Re: [SQL] INSERT INTO relational tables
Thanks for your hints so far.
I'm looking for both syntax simplicity and referential integrity. I'm using c++
with libpqxx, but I didn't find a good object-relational mapper. And I'm too
lazy to implement a whole persistency layer as I don't have a huge DB with many
relations.
I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I created the
VIEW composing my table with customers and orders:
CREATE VIEW vw_customer_insert AS
SELECT * FROM tbl_customer, tbl_order
WHERE cust_id = ord_customer;
Then I added a rule for the inserting:
CREATE RULE rl_customer_insert AS
ON INSERT TO vw_customer_insert DO INSTEAD
( INSERT INTO tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address);
INSERT INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"'));
);
But this results in the same problem: It works fine if I insert just one new
customer with one new order. But if I want to insert several new entries:
INSERT INTO vw_customer_insert(cust_name, cust_address, ord_pieces,
ord_productname, ord_price)
VALUES ), (‘MR. X’, ‘1st street’, 5, ‘books’, 50), (‘MRS. Y’, ‘2nd street’,
1, ‘t-shirt’, 10);
This doesn't work correctly; all orders are now related to the last customer.
In this example, both orders are related to MRS. Y because I use currval() in
my insert rule.
Do you see any solution for this? I thought that the performance of this would
be better than INSERTing to tbl_customer, fetching the ID and then do several
INSERTS to tbl_order in c++. But actually I tend to do it with several INSERT
statements in one transaction, as Steve proposed.
Cheers Stefan
--
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] INSERT INTO relational tables
am Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger folgendes:
> Thanks for your hints so far.
>
> I'm looking for both syntax simplicity and referential integrity. I'm
> using c++ with libpqxx, but I didn't find a good object-relational
> mapper. And I'm too lazy to implement a whole persistency layer as I
> don't have a huge DB with many relations.
>
> I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I
> created the VIEW composing my table with customers and orders:
>
> CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer,
> tbl_order WHERE cust_id = ord_customer;
>
> Then I added a rule for the inserting: CREATE RULE rl_customer_insert
> AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO
> tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT
> INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
> NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"')););
>
> But this results in the same problem: It works fine if I insert just
> one new customer with one new order. But if I want to insert several
> new entries:
Insert the new customer only into the table if this customer doesn't
exist there. If the customer exist in the table, obtain the id for this
customer.
simplified:
- detect, if the customer exists
yes: obtain the id as id
no: insert and use currval() as id
- insert the order with the id
I would write a function for this.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] INSERT INTO relational tables
am Mon, dem 10.12.2007, um 12:08:48 +0100 mailte A. Kretschmer folgendes:
> am Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger
> folgendes:
> > Thanks for your hints so far.
> >
> > I'm looking for both syntax simplicity and referential integrity. I'm
> > using c++ with libpqxx, but I didn't find a good object-relational
> > mapper. And I'm too lazy to implement a whole persistency layer as I
> > don't have a huge DB with many relations.
> >
> > I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I
> > created the VIEW composing my table with customers and orders:
> >
> > CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer,
> > tbl_order WHERE cust_id = ord_customer;
> >
> > Then I added a rule for the inserting: CREATE RULE rl_customer_insert
> > AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO
> > tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT
> > INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
> > NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"')););
> >
> > But this results in the same problem: It works fine if I insert just
> > one new customer with one new order. But if I want to insert several
> > new entries:
>
> Insert the new customer only into the table if this customer doesn't
> exist there. If the customer exist in the table, obtain the id for this
> customer.
>
>
>
> simplified:
>
> - detect, if the customer exists
> yes: obtain the id as id
> no: insert and use currval() as id
> - insert the order with the id
>
>
> I would write a function for this.
a little example:
test=# create table customer (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence "customer_id_seq" for
serial column "customer.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey"
for table "customer"
CREATE TABLE
test=*# create table orders (customer int references customer, val int);
CREATE TABLE
test=*# create or replace function orders_insert (text, int) returns int
as $$declare i int;begin select into i id from customer where name=$1;
if i is null then insert into customer (name) values ($1); select into i
currval('customer_id_seq'); end if; insert into orders values (i,$2);
return i; end; $$language plpgsql;
CREATE FUNCTION
test=*# select * from customer;
id | name
+--
(0 rows)
test=*# select * from orders ;
customer | val
--+-
(0 rows)
test=*# select orders_insert('foo',1);
orders_insert
---
1
(1 row)
test=*# select orders_insert('foo',2);
orders_insert
---
1
(1 row)
test=*# select orders_insert('foo',3);
orders_insert
---
1
(1 row)
test=*# select orders_insert('bar',4);
orders_insert
---
2
(1 row)
test=*# select * from customer;
id | name
+--
1 | foo
2 | bar
(2 rows)
test=*# select * from orders ;
customer | val
--+-
1 | 1
1 | 2
1 | 3
2 | 4
(4 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] INSERT INTO relational tables
Original-Nachricht
> Datum: Mon, 10 Dec 2007 12:29:15 +0100
> Von: "A. Kretschmer" <[EMAIL PROTECTED]>
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] INSERT INTO relational tables
> am Mon, dem 10.12.2007, um 12:08:48 +0100 mailte A. Kretschmer folgendes:
> > am Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger
> folgendes:
> > > Thanks for your hints so far.
> > >
> > > I'm looking for both syntax simplicity and referential integrity. I'm
> > > using c++ with libpqxx, but I didn't find a good object-relational
> > > mapper. And I'm too lazy to implement a whole persistency layer as I
> > > don't have a huge DB with many relations.
> > >
> > > I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I
> > > created the VIEW composing my table with customers and orders:
> > >
> > > CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer,
> > > tbl_order WHERE cust_id = ord_customer;
> > >
> > > Then I added a rule for the inserting: CREATE RULE rl_customer_insert
> > > AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO
> > > tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT
> > > INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
> > > NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"')););
> > >
> > > But this results in the same problem: It works fine if I insert just
> > > one new customer with one new order. But if I want to insert several
> > > new entries:
> >
> > Insert the new customer only into the table if this customer doesn't
> > exist there. If the customer exist in the table, obtain the id for this
> > customer.
> >
> >
> >
> > simplified:
> >
> > - detect, if the customer exists
> > yes: obtain the id as id
> > no: insert and use currval() as id
> > - insert the order with the id
> >
> >
> > I would write a function for this.
>
> a little example:
>
> test=# create table customer (id serial primary key, name text);
> NOTICE: CREATE TABLE will create implicit sequence "customer_id_seq" for
> serial column "customer.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "customer_pkey" for table "customer"
> CREATE TABLE
> test=*# create table orders (customer int references customer, val int);
> CREATE TABLE
> test=*# create or replace function orders_insert (text, int) returns int
> as $$declare i int;begin select into i id from customer where name=$1;
> if i is null then insert into customer (name) values ($1); select into i
> currval('customer_id_seq'); end if; insert into orders values (i,$2);
> return i; end; $$language plpgsql;
> CREATE FUNCTION
> test=*# select * from customer;
> id | name
> +--
> (0 rows)
>
> test=*# select * from orders ;
> customer | val
> --+-
> (0 rows)
>
> test=*# select orders_insert('foo',1);
> orders_insert
> ---
> 1
> (1 row)
>
> test=*# select orders_insert('foo',2);
> orders_insert
> ---
> 1
> (1 row)
>
> test=*# select orders_insert('foo',3);
> orders_insert
> ---
> 1
> (1 row)
>
> test=*# select orders_insert('bar',4);
> orders_insert
> ---
> 2
> (1 row)
>
> test=*# select * from customer;
> id | name
> +--
> 1 | foo
> 2 | bar
> (2 rows)
>
> test=*# select * from orders ;
> customer | val
> --+-
> 1 | 1
> 1 | 2
> 1 | 3
> 2 | 4
> (4 rows)
>
Thanks a lot, this is exactly what I was looking for.
I realize I need to learn more about functions in SQL. Hopefully my questions
will be more sophisticated next time ;-)
Greets Stefan
--
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] SQL INSERT/TRIGGER Help
Poovendran Moodley escribió: > I'm not really sure how to the *currval() *method. I've read up on it and I > noticed it works with *nextval()* and *setval()*. The parameter for * > currval()* is a regex - is there a regex to represent the most recently > automatically generated number ( i.e. a serial field)? It's not a regex. I assume you are confused because it says "regclass". This is shorthand for "registered class" (where "class" is a synonymous for "relation", in this case a sequence). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "La felicidad no es mañana. La felicidad es ahora" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Insert Rule, Multiple Insert, Update Problem
Hi, I am having trouble with an insert rule that also does an update. It works fine for a single insert but multiple inserts in a single statement don't behave as I expected. An outline of the problem is: A table "trans" has a "client_id" and a transaction amount called "points". Another table "clienst" also holds the "client_id" and an accumulated amount "total_points". I create a rule for the trans table: create rule trans_insert as on insert to trans do also update clients set total_points = total_points + NEW.points; where client_id = NEW.client_id; This works fine for a single insert but if I dump multiple transactions for multiple clients in the trans table only the first transaction for each client is accounted for in clients.total_points. I am just about to try and implement this as a trigger but it is driving me crazy as to why this won't work as a rule. If I put something like the following: insert into foo values(NEW.points); Then every transaction points value is copied to foo. Any help is very appreciated. Thanks, Scott. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] join on three tables is slow
Ok, I've been playing around with this SQL some more and I found that if I remove this: e.active = '1' from the query that the query now completes in 5 seconds. Nothing else has anywhere near the impact of this boolean condition. So what is it about this boolean field that is causing so much delay? Gerry ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Insert Rule, Multiple Insert, Update Problem
--- On Mon, 12/10/07, Scott <[EMAIL PROTECTED]> wrote: > I am having trouble with an insert rule that also does an > update. It works > fine for a single insert but multiple inserts in a single > statement don't > behave as I expected. Yup, that is the limitation of rules. They are only useful if you issue statements that will only affect one tuple in a table at a time. If you want something different, you will have to use functions or triggers. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join on three tables is slow
On 11/12/2007, Gerry Reno <[EMAIL PROTECTED]> wrote: > Ok, I've been playing around with this SQL some more and I found that if > I remove this: > e.active = '1' > from the query that the query now completes in 5 seconds. Nothing else > has anywhere near the impact of this boolean condition. So what is it > about this boolean field that is causing so much delay? > send execution plan and we can see. Maybe you need a cast on every row, because '1' is varchar. Try e.active = true Pavel > Gerry > > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
