[SQL] Table Join (Maybe?)

[Phillip Smith]

Hi again all,

 

I have two tables:

1. Sales figures by date and
customer.

2. Customer details –
including their Geographic State

 

I need to extract a report from the first table (I can do
that!), and in that report order by their State (I can do that too!), but I
also need a summary of all the customers in each state, below the end of each
state, and have a grand total at the bottom.

 

Eg:

Customer 1  State 1 $100.00

Customer 2  State 1 $100.00

State
1 $200.00

Customer 3  State 2 $100.00

Customer 4  State 2 $100.00

State
2 $200.00

Grand Total $400.00

 

Does anyone have any magic pointers for me? I’ve been
playing with SELECT INTO as 2 queries (the individual customers, then the
summary figures added to the temp table) but I end up with ROWS IN FIRST QUERY *
ROWS IN SECOND QUERY instead of them all sorted together nicely L

 

Thanks all,

-p

 

Table Defs:

CREATE TABLE sales_figures

(

  rep_date date NOT NULL,

  store varchar(6) NOT NULL,

  sales_value numeric DEFAULT 0,

  sales_customers int4 DEFAULT 0,

  CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date,
store),

  CONSTRAINT sales_figures_store FOREIGN KEY
(store)

  REFERENCES stores
(code) MATCH SIMPLE

  ON UPDATE NO ACTION
ON DELETE NO ACTION

)

 

CREATE TABLE stores

(

  code varchar(2) NOT NULL DEFAULT
''::character varying,

  name varchar(32) NOT NULL DEFAULT
''::character varying,

  bms varchar(1) DEFAULT 'Y'::character
varying,

  state text DEFAULT 'UNKNOWN'::text,

  business_open varchar(1) DEFAULT 'Y'::character
varying,

  CONSTRAINT stores_pkey PRIMARY KEY (code)

)

 

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA 

 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] INSERT/UPDATEs cycles and lack of phantom locking

[Florian Weimer]

I've got several tables where I need to either insert new records, or
update existing ones (identified based on the primary key).  For
performance reasons, I want to do this in batches, so I plan to use
something like this:

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- SERIALIZABLE
...
CREATE TEMPORARY TABLE tmp
  (key TEXT NOT NULL, new_val INTEGER NOT NULL);
COPY tmp (key, new_val) FROM STDIN;
...
\.
-- SAVEPOINT tmp_created;  -- (see below)

CREATE TEMPORARY TABLE tmp2 AS SELECT tmp.key, new_val, real.val AS old_val
  FROM tmp LEFT OUTER JOIN real ON tmp.key = real.key;
UPDATE real SET val = new_val + old_val FROM tmp2
  WHERE old_val IS NOT NULL AND tmp2.key = real.key;
INSERT INTO real SELECT key, new_val FROM tmp2 WHERE old_val IS NULL;

If this is run concurrently, the INSERT may fail.  In this case, I
rerun the transaction.  Actually, I want to rollback to the
tmp_created checkpoint, but I don't think this will pick up the new
rows in the "real" table, and the INSERT will fail again.

Usually, the batch size is small enough that the necessary data is
still cached, and concurrent updates aren't the norm, so this approach
(complete transaction rollback) is not completely infeasible.

However, I still wonder if there is a more straightforward solution.
Serializing the updates isn't one, I think.  Is there some form of
table-based advisory locking which I could use?  This way, I wouldn't
lock out ordinary readers (which is crucial), but the reading part of
an updating transaction would be blocked.  For bonus points, deadlocks
would be automatically detected by PostgreSQL (although I would order
the locks properly in the usual case, but I can't guarantee this for
all codepaths due to the modularity of the application).

Florian
-- 
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH   http://www.bfk.de/
Durlacher Allee 47tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Storing encrypted data?

[John Tregea]

Hi Aaron,

I found that your suggestion worked well. For some reason the IDE I use 
(Revolution) put a return character every 73rd character when it did the 
base64encode, but I strip those out and there no further problems. I 
don't even have to put them back later to decode it.


Thanks

John T

Aaron Bono wrote:
On 7/17/06, *John Tregea* <[EMAIL PROTECTED] 
> wrote:


Hi Michael,

Thanks for your advice. I was looking at the bytea data type in the
PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
was concerned that if I have to escape certain characters like the
single quote or the backslash, how do I guarantee that the escaped
version does not already appear in the encrypted string?

Should I use the octal value to escape the single quote (\047) and
backslash (\\134)?

Those character sequences are extremely unlikely to occur in an
encrypted string.

Is the the right approach ?

Also... I note that I cannot change the data type of my field from
text
to bytea (I am using PGADMIN III). Do you know why? 



 
When I have data like this, I do a Base64 encoding.  The string ends 
up longer but I no longer have to worry about special characters 
mucking things up.


==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
== 


---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Use of cmin in psql

[sathiya moorthy]

hi,

what is the use of the cmin system column

After inserting if i see this column cmin has the 0 in the ordinary places., 

If it is in the transaction place ( between BEGIN and END ) where i had
deleted and rollbacked that column has the 33397398 somw what large
number..-- sathiyamoorthy

Re: [SQL] Table Join (Maybe?)

[Richard Broersma Jr]

> I have two tables: 
> 1. Sales figures by date and customer.
> 2. Customer details - including their Geographic State
> I need to extract a report from the first table (I can do that!), and in
> that report order by their State (I can do that too!), but I also need a
> summary of all the customers in each state, below the end of each state, and
> have a grand total at the bottom.
> Eg:
> Customer 1  State 1 $100.00
> Customer 2  State 1 $100.00
> State 1 $200.00
> Customer 3  State 2 $100.00
> Customer 4  State 2 $100.00
> State 2 $200.00
> Grand Total $400.00
> Does anyone have any magic pointers for me? I've been playing with SELECT
> INTO as 2 queries (the individual customers, then the summary figures added
> to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN SECOND
> QUERY instead of them all sorted together nicely :-(

I do not believe that a single "query" will give you what you want here.  YOU 
could have 3
seperate queries:
1 for customer detail by state.
1 for state total 
1 for grand total

However, if you use a reporting tool like crystal or others, they have the 
ability to generate
summaries exactly as you are referring to here.  you would only need to pass it 
the query on total
per customer.  The reporting utility has a groupby feature where it would group 
the customers by
state for your.  In the group by summary it would automaticly display state 
total.  Then if the
report footer you could add a grand total summary for all of the records you 
passed to the report.

The following link show some of the reporting programs that you can use.
http://www.postgresql.org/community/survey.43 

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[SQL] User Permission

[sathish kumar shanmugavelu]

Dear group,   i created a user named 'dataviewer' and grant only select permission to that user,   but now the user could able to create tables. how to restrict this,   i want to give permission to create views and do selects on tables and views.
   how to do it?   plz help.-- Sathish Kumar.SSpireTEK

Re: [SQL] Sequences, values still increased

[Chris Browne]

Paul Maier <[EMAIL PROTECTED]> writes:
> Hello everybody,
>
> Why does...
>
> BEGIN;
> SELECT nextval('test.test_seq') AS id;
> ROLLBACK;
>
> ...still increase the sequence after aborting the transaction? This
> nextval-command should be reverted after rolling back, right? Same with an
> ABORT:
>
> BEGIN;
> SELECT nextval('test.test_seq') AS id;
> ABORT;
>
> Can anybody help me with this? Thanks a lot!

nextval does NOT roll back.

As per the Fine Documentation on Sequence Manipulation Functions:

"Important: To avoid blocking of concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never rolled
back; that is, once a value has been fetched it is considered used,
even if the transaction that did the nextval later aborts. This means
that aborted transactions may leave unused "holes" in the sequence of
assigned values. setval operations are never rolled back, either."
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/lsf.html
I'm as confused as a baby in a topless bar.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking

[Tom Lane]

Florian Weimer <[EMAIL PROTECTED]> writes:
> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- SERIALIZABLE
> ...
> -- SAVEPOINT tmp_created;  -- (see below)

> CREATE TEMPORARY TABLE tmp2 AS SELECT tmp.key, new_val, real.val AS old_val
>   FROM tmp LEFT OUTER JOIN real ON tmp.key = real.key;
> UPDATE real SET val = new_val + old_val FROM tmp2
>   WHERE old_val IS NOT NULL AND tmp2.key = real.key;
> INSERT INTO real SELECT key, new_val FROM tmp2 WHERE old_val IS NULL;

> If this is run concurrently, the INSERT may fail.  In this case, I
> rerun the transaction.  Actually, I want to rollback to the
> tmp_created checkpoint, but I don't think this will pick up the new
> rows in the "real" table, and the INSERT will fail again.

Why do you think that?  If you're running in READ COMMITTED mode then
each statement takes a new snapshot.

regards, tom lane

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

Re: [SQL] Table Join (Maybe?)

[Erik Jones]

Phillip Smith wrote:


Hi again all,

I have two tables:

1. Sales figures by date and customer.

2. Customer details – including their Geographic State

I need to extract a report from the first table (I can do that!), and 
in that report order by their State (I can do that too!), but I also 
need a summary of all the customers in each state, below the end of 
each state, and have a grand total at the bottom.


Eg:

Customer 1 State 1 $100.00

Customer 2 State 1 $100.00

State 1 $200.00

Customer 3 State 2 $100.00

Customer 4 State 2 $100.00

State 2 $200.00

Grand Total $400.00

Does anyone have any magic pointers for me? I’ve been playing with 
SELECT INTO as 2 queries (the individual customers, then the summary 
figures added to the temp table) but I end up with ROWS IN FIRST QUERY 
* ROWS IN SECOND QUERY instead of them all sorted together nicely L


Thanks all,

-p

Well, two queries one for the individual totals and one for the summary 
totals is good (maybe a third for the grand total), but you should do a 
union of the two and then play with the order by and/or group by clauses 
(depending on the data) to get the ordering that you want. I can't even 
count the times I've spent banging my head against the proverbial wall 
(you do have a proverbial wall don't you?) trying to get these kinds of 
queries to work with joins, sub-queries, case statements, etc... only to 
come back to using union on simple, to-the-point queries.


--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


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

Re: [SQL] Storing encrypted data?

[Aaron Bono]

On 7/19/06, John Tregea <[EMAIL PROTECTED]> wrote:
Hi Aaron,I found that your suggestion worked well. For some reason the IDE I use(Revolution) put a return character every 73rd character when it did thebase64encode, but I strip those out and there no further problems. I
don't even have to put them back later to decode it. I usually leave the return characters where they are.I am curious, why did you decide to remove the return characters?
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com==

Re: [SQL] Use of cmin in psql

[Tom Lane]

"sathiya moorthy" <[EMAIL PROTECTED]> writes:
> what is the use of the cmin system column

http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] User Permission

[Aaron Bono]

On 7/19/06, sathish kumar shanmugavelu <[EMAIL PROTECTED]> wrote:
Dear group,   i created a user named 'dataviewer' and grant only select permission to that user,   but now the user could able to create tables. how to restrict this,   i want to give permission to create views and do selects on tables and views.
   how to do it?   plz help. Have your checked http://www.postgresql.org/docs/8.1/interactive/sql-grant.html
http://www.postgresql.org/docs/8.1/interactive/sql-revoke.htmlI would start by creating a role:
http://www.postgresql.org/docs/8.1/interactive/user-manag.htmlAnd revoke all on it.  Then add only the permissions it needs and assign the role to the user.==
   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com==

Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking

[Florian Weimer]

* Tom Lane:

> Why do you think that?  If you're running in READ COMMITTED mode then
> each statement takes a new snapshot.

Ah, I should have explained that.  I might need the SERIALIZABLE
isolation level in the future (this code doesn't need it, but other
things in the same transaction might require it).

In addition, it occurred to me that I get the INSERT failure only if
there is a suitable PRIMARY KEY/UNIQUE constraint on the table.  I
haven't got that in all cases, so I need that advisory locking anyway,
I fear.

-- 
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH   http://www.bfk.de/
Durlacher Allee 47tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] User Permission

[Richard Broersma Jr]

> > Dear group,
> >i created a user named 'dataviewer' and grant only select permission to
> > that user,
> >but now the user could able to create tables. how to restrict this,
> >i want to give permission to create views and do selects on tables and
> > views.
> >how to do it?
> >plz help.
> Have your checked
> http://www.postgresql.org/docs/8.1/interactive/sql-grant.html
> http://www.postgresql.org/docs/8.1/interactive/sql-revoke.html
> I would start by creating a role:
> http://www.postgresql.org/docs/8.1/interactive/user-manag.html
> And revoke all on it.  Then add only the permissions it needs and assign the
> role to the user.

Also, one additional point would be to revoke all from public as mentioned in 
the following
thread:
http://archives.postgresql.org/pgsql-general/2006-07/msg00148.php

Apparently, whatever privileges 'pubic' has are extended to the privileges of 
the individual
users.

Regards,

Richard Broersma Jr.

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

   http://archives.postgresql.org

[SQL] Multi-table insert using RULE - how to handle id?

[Collin Peters]

I am learning about how to use rules to handle a multi-table insert.
Right now I have a user_activity table which tracks history and a
user_activity_users table which tracks what users are associated with
a row in user_activity (one to many relationship).

I created a rule (and a view called user_activity_single) which is to
simplify the case of inserting a row in user_activity in which there
is only one user in user_activity_users.

CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
NEW.user_activity_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
NEW.user_activity_id,
NEW.user_id
);
);

This works well by itself, but the problem is that I have to manually
pass in the user_activity_id which is the primary key.  I do this by
calling nextval to get the next ID in the sequence.

Is there any way to have the rule handle the primary key so I don't
have to pass it in?  It seems you can't use pgsql inside the rule at
all.  What I'm looking for is something like:

CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
SELECT nextval('user_activity_user_activity_id_seq') INTO next_id;

INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
next_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
next_id,
NEW.user_id
);
);
Note the sequence stored in next_id.  This doesn't work as it
complains about next_id in the INSERT statements.  Any way to do
something like this?  I suppose I could create a function and then
have the rule call the function but this seems like overkill.

Regards,
Collin

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

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

FW: [SQL] Table Join (Maybe?)

[Phillip Smith]

Thanks all for your suggestions – the
below suggestion works perfectly.

 

A little tweak of the column counts and
group by clauses makes it work like a charm.

 

Richard – this particular extract is
via PHP to a web page so I can’t use those reporting tools, but I’ll
have a look and fiddle for my next project – Thanks!

 

Cheers,

-p

 

-Original Message-
From: Arulmani V A
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday,
 19 July 2006 20:49
To: Phillip Smith
Subject: Re: [SQL] Table Join
(Maybe?)

 





Hi,

Postgres does not seem to support OLAP functions like ROLLUP, CUBE, etc. I'm
not a Postgres expert, but I think we can try the following alternative
approach :

Sample data for the two tables given by you are created as :

Table : stores
code     name         bms    
state         business_open
1        Customer1   
Y        State1   
    Y
2        Customer2   
Y        State2   
    Y
3        Customer3   
Y        State1   
    Y
4        Customer4   
Y        State2   
    Y

Table : sales_figures
rep_date     store     sales_value
    sales_customers
2006-01-01    1       
1000            0
2006-01-02    1       
1000            0
2006-02-01    2       
200               
0
2006-02-02    2       
200               
0
2006-02-03    2       
500               
0
2006-01-03    1       
100               
0
2006-01-01    3       
300               
0
2006-01-02    3       
200               
0
2006-02-01    4       
700               
0
2006-02-02    4       
400               
0

If I execute the following query :
SELECT a.name, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE
a.code = b.store GROUP BY a.state, a.name
UNION
SELECT NULL, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE
a.code = b.store GROUP BY a.state
 UNION
SELECT 'GRAND TOTAL', NULL, SUM(b.sales_value) FROM stores a, sales_figures b
WHERE a.code = 
b.store ORDER BY 2

I get the following output (same as what you expect?) :

name        state    sum

Customer1    State1    2100
Customer3    State1    500
NULL        State1    2600
Customer2    State2    900
Customer4    State2    1100
NULL        State2    2000
GRAND TOTAL    NULL    4600

Is the above approach OK?

Regards
Arul



- Original Message

From: Phillip Smith <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Wednesday, July 19, 2006 1:04:58 PM
Subject: [SQL] Table Join (Maybe?)



Hi again all,

 

I have two tables:

1. Sales figures by date and
customer.

2. Customer details –
including their Geographic State

 

I need to extract a report from the first table (I can do
that!), and in that report order by their State (I can do that too!), but I
also need a summary of all the customers in each state, below the end of each
state, and have a grand total at the bottom.

 

Eg:

Customer 1  State
1 $100.00

Customer 2  State
1 $100.00

State 1 $200.00

Customer 3  State
2 $100.00

Customer 4  State
2 $100.00

State 2 $200.00

Grand
Total
$400.00

 

Does anyone have any magic pointers for me? I’ve been playing
with SELECT INTO as 2 queries (the individual customers, then the summary
figures added to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS
IN SECOND QUERY instead of them all sorted together nicely L

 

Thanks all,

-p

 

Table Defs:

CREATE TABLE sales_figures

(

  rep_date date NOT
NULL,

  store varchar(6) NOT
NULL,

  sales_value numeric
DEFAULT 0,

  sales_customers int4
DEFAULT 0,

  CONSTRAINT
sales_figures_pkey PRIMARY KEY (rep_date, store),

  CONSTRAINT
sales_figures_store FOREIGN KEY (store)

 
REFERENCES stores (code) MATCH SIMPLE

 
ON UPDATE NO ACTION ON DELETE NO ACTION

)

 

CREATE TABLE stores

(

  code varchar(2) NOT
NULL DEFAULT ''::character varying,

  name varchar(32) NOT
NULL DEFAULT ''::character varying,

  bms varchar(1)
DEFAULT 'Y'::character varying,

  state text DEFAULT
'UNKNOWN'::text,

  business_open
varchar(1) DEFAULT 'Y'::character varying,

  CONSTRAINT
stores_pkey PRIMARY KEY (code)

)

 

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA 

 



 

***Confidentiality and Privilege
Notice*** 

The
material contained in this message is privileged and confidential to the
addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy or
deliver this message to anyone, and you should destroy it and kindly notify the
sender by reply email. 

Information
in this message that does not relate to the official business of Weatherbeeta
must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta,
its employees, contractors or associates shall not be liable for direct,
indirect or consequential loss arising from transmission of this message or any
attachments 



 









***Confidentiality and Privilege Notice***

The m

Re: [SQL] Multi-table insert using RULE - how to handle id?

[Aaron Bono]

On 7/19/06, Collin Peters <[EMAIL PROTECTED]> wrote:
I am learning about how to use rules to handle a multi-table insert.Right now I have a user_activity table which tracks history and auser_activity_users table which tracks what users are associated witha row in user_activity (one to many relationship).
I created a rule (and a view called user_activity_single) which is tosimplify the case of inserting a row in user_activity in which thereis only one user in user_activity_users.CREATE OR REPLACE RULE user_activity_single_insert AS
 ON INSERT TO user_activity_singleDO INSTEAD (INSERT INTO user_activity(user_activity_id,description,...)VALUES (
NEW.user_activity_id,NEW.description,...);INSERT INTO user_activity_users (user_activity_id,user_id
)VALUES (NEW.user_activity_id,NEW.user_id););This works well by itself, but the problem is that I have to manuallypass in the user_activity_id which is the primary key.  I do this by
calling nextval to get the next ID in the sequence.Is there any way to have the rule handle the primary key so I don'thave to pass it in?  It seems you can't use pgsql inside the rule atall.  What I'm looking for is something like:
CREATE OR REPLACE RULE user_activity_single_insert AS ON INSERT TO user_activity_singleDO INSTEAD (SELECT nextval('user_activity_user_activity_id_seq') INTO next_id;INSERT INTO user_activity(
user_activity_id,description,...)VALUES (next_id,NEW.description,...);
INSERT INTO user_activity_users (user_activity_id,user_id)VALUES (next_id,NEW.user_id););
Note the sequence stored in next_id.  This doesn't work as itcomplains about next_id in the INSERT statements.  Any way to dosomething like this?  I suppose I could create a function and thenhave the rule call the function but this seems like overkill.
 Since I have not tried something like this before, I may be off base but have you tried:CREATE OR REPLACE RULE user_activity_single_insert AS ON INSERT TO user_activity_singleDO INSTEAD (
        INSERT INTO user_activity(                description,                ...        )        VALUES (                NEW.description,                ...        );        INSERT INTO user_activity_users (
                user_activity_id,                user_id        )        VALUES (                SELECT currval('user_activity_user_activity_id_seq'),                NEW.user_id        ););
I am assuming user_activity.user_activity_id is a BIGSERIAL or SERIAL.==   Aaron Bono   Aranya Software Technologies, Inc.   
http://www.aranya.com==

Re: [SQL] Multi-table insert using RULE - how to handle id?

[Ross Johnson]

Aaron Bono wrote:

On 7/19/06, *Collin Peters* <[EMAIL PROTECTED] 
> wrote:


I am learning about how to use rules to handle a multi-table insert.
Right now I have a user_activity table which tracks history and a
user_activity_users table which tracks what users are associated with
a row in user_activity (one to many relationship).

I created a rule (and a view called user_activity_single) which is to
simplify the case of inserting a row in user_activity in which there
is only one user in user_activity_users.

CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
NEW.user_activity_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
NEW.user_activity_id,
NEW.user_id
);
);

This works well by itself, but the problem is that I have to manually
pass in the user_activity_id which is the primary key.  I do this by
calling nextval to get the next ID in the sequence.

Is there any way to have the rule handle the primary key so I don't
have to pass it in?  It seems you can't use pgsql inside the rule at
all.  What I'm looking for is something like:

CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
SELECT nextval('user_activity_user_activity_id_seq') INTO
next_id;

INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
next_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
next_id,
NEW.user_id
);
);
Note the sequence stored in next_id.  This doesn't work as it
complains about next_id in the INSERT statements.  Any way to do
something like this?  I suppose I could create a function and then
have the rule call the function but this seems like overkill. 



 
Since I have not tried something like this before, I may be off base 
but have you tried:


CREATE OR REPLACE RULE user_activity_single_insert AS
 ON INSERT TO user_activity_single
DO INSTEAD (
   INSERT INTO user_activity(
   description,
   ...
   )
   VALUES (
   NEW.description,
   ...
   );
   INSERT INTO user_activity_users (
   user_activity_id,
   user_id
   )
   VALUES (
   SELECT currval('user_activity_user_activity_id_seq'),
   NEW.user_id
   );
);

I am assuming user_activity.user_activity_id is a BIGSERIAL or SERIAL.


By coincidence, I had to work this out just the other day for a project 
that I'm working on. Aaron's solution looks correct, although you can 
just call currval() without the SELECT in this context.


What Collin may not be aware of is that currval(seqX) returns the last 
value generated by nextval(seqX) in the same session, so there is no 
race between your session and other sessions that may be using the same 
sequence. So it's safe to let the insert trigger the next value 
automatically, and then call currval(). In contrast, don't use lastval() 
for this.


Ross Johnson


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings