7; as c
FROM
my_table t;
Does this seem reasonable to you? Do you problems with this approach?
Thanks again for your help,
Chad
From: Robins Tharakan [mailto:[EMAIL PROTECTED]
Sent: Monday, April 14, 2008 8:34 PM
To: Chad Showalter
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] r
Hi Chad,
(Pardon me if I am shooting the stars here...)
Don't you think that on each update, you would be creating a new row that
satisfies that very given condition for the view ?
By that I mean that when you do a 'UPDATE... WHERE my_table_id=1' the RULE
now inserts another row with my_table_id
I would like to create a rule that, by updating a view, allows me to update
one table and insert into another.
The following example illustrates what I'm trying to do:
--Create Tables
CREATE TABLE my_table
(
my_table_id serial,
a character varying(255),
Here's what I'd like to happen, but I'm not seeing how it can be done.
Say we have this simple table:
CREATE TABLE foo (
id integer,
foo varchar
);
and then many tables along these lines:
CREATE TABLE ud1_foo (LIKE foo);
CREATE TABLE ud2_foo (LIKE foo);
What I'd like is to do is select agains
am Fri, dem 05.10.2007, um 7:16:06 +0800 mailte Hengky Lie folgendes:
> Yes, it works now ! Wow, the problem is in the field name. Changed it to
> lowercase solved the problem. Thank you to all ho give me this advice.
>
> But now I have another question regarding to this field, what command I ca
between these 2 tables (tblmasdbt and tblmasgl) ?
Thank you so much.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of A. Kretschmer
Sent: 04 Oktober 2007 21:00
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Rule Error
am Thu, dem 04.10.2007, um 19:22:32
am Thu, dem 04.10.2007, um 19:22:32 +0800 mailte Hengky Lie folgendes:
> CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
>
> DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
>
>
>
> But I always get this error :
>
>
> ERROR: column "kodegl" of relation
You have defined the fields KODEGL and NAMAREK as uppercased field names.
In your rule you refer to an unquoted field KODEGL twice and twice to an
unquoted field NAMAREK.
Default behaviour of PostgreSQL for unquoted fieldnames is to lowercase them.
As such these fields effectively don't exist in y
Hengky Lie wrote:
I have 2 tables : tblmasdbt and tblmasgl.
I want on every record insertion in tblmasdbt, that record also
automatically insert into tblmasdbt. I need only 2 related field.
You probably want triggers rather than rules, but anyway.
CREATE RULE "rule1" AS ON INSERT TO "publi
Dear Friends,
I have problem with rule and tried several times to solve it but not yet
success. Hope someone can help me.
I have 2 tables : tblmasdbt and tblmasgl.
I want on every record insertion in tblmasdbt, that record also
automatically insert into tblmasdbt. I need only 2 relate
Subject: [SQL] Rule for multiple entries
Hi all!
I have a rule in place that is supposed to adjust a value in one
table based on how many rows are added or deleted to another table,
but I'm not getting the results that I hoped for. If a single sql
statement adds or deletes multiple en
Hi all!
I have a rule in place that is supposed to adjust a value in one
table based on how many rows are added or deleted to another table,
but I'm not getting the results that I hoped for. If a single sql
statement adds or deletes multiple entries, the rule still only
adjusts the value by
Ken Winter wrote:
Richard ~
Let me zoom out for a moment, for the bigger picture.
As you have inferred, what I'm trying to do is develop a history-preserving
table ("my_data" in the example that started this thread). *Most* user
programs would see and manipulate this table as if it contained o
nks for your help. I hope this little essay is of some value to others.
~ Ken
> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 19, 2005 11:05 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL]
Richard Huxton writes:
> Ken Winter wrote:
>> Can a trigger be written on a *view*?
> There's nothing for them to fire against even if you could attach the
> trigger.
Currently we reject CREATE TRIGGER on a view, but it occurred to me the
other day that that could be relaxed, at least for BEFOR
Ken Winter wrote:
Richard ~
Thanks for your response.
Can a trigger be written on a *view*? I can't find anything in the
PostgreSQL docs that answers this question.
There's nothing for them to fire against even if you could attach the
trigger. I suppose you could have a statement-level trig
ns was as rules.
~ Ken
> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 19, 2005 4:08 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> >
Ken Winter wrote:
This rule is supposed to (1) cause an update directed to the view
"my_data_now" to be made to the underlying table "my_data", (2) reset the
"effective_date_and_time" of that row to 'now', (3) insert a record
containing the old values into "my_data", and (4) expire that "old" rec
I'm trying to figure out why a rule gives me a uniqueness violation when I
try to do an update.
I have a table, "my_data", defined as:
create table my_data (
id INT8 not null default nextval('person_seq'),
effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
CURRENT_T
Personally I feel that if this individual can't be bothered to white
list the postgresql.org domain they should be banned from the list.
Kind Regards,
Keith
Original Message
Subject:RE: Re: [SQL] Rule
Date: Wed, 8 Jun 2005 19:02:39 -0300 (BRT)
From: AntiSpa
David wrote:
What I want to do is setup some kind of rule so that whenever a s_data
field is updated, that the time_stamp gets update to the current time/date.
Normally you want to do that with a before trigger rather than a rule.
Ok, I have no knowledge of Tiggers except what I just read i
Ok, I have no knowledge of Tiggers except what I just read in the docs
section. Look like I need to make a procudure then call it with a trigger.
Is there a better location for Tigger/Procudure Examples. The trigger seems
fairly, however I got lost in the procudure part.
David
Normally you
On Wed, Jun 08, 2005 at 15:51:35 -0400,
David Hofmann <[EMAIL PROTECTED]> wrote:
>
> What I want to do is setup some kind of rule so that whenever a s_data
> field is updated, that the time_stamp gets update to the current time/date.
Normally you want to do that with a before trigger rather t
I have a table with 3 fields, id, s_data, and time_stamp. The time_stamp
field is set to now() by deault.
The program that uses this table only uses the id and s_data file. I added
and use the time_stamp field to delete old records after a certain time.
What I want to do is setup some kind
M.D.G. Lange wrote:
In order for a "dictionary" system I have created the following system:
tbllanguages
- ID
- name
Primary key ( ID )
tbldictionary
- wordid
- languageid
- value
Primary key ( wordid, languageid)
The idea is to have a word id in several languages, so you only have to
look up
On Tue, 7 Jun 2005, M.D.G. Lange wrote:
> It is not possible to create a constraint Foreign key for "wordid". No
> problem there, but I want to be certain that a given wordid exists in
> tbldictionary.
> Would I have to create a "RULE" or a "TRIGGER" to be certain that the
> wordid is existing in
In order for a "dictionary" system I have created the following system:
tbllanguages
- ID
- name
Primary key ( ID )
tbldictionary
- wordid
- languageid
- value
Primary key ( wordid, languageid)
The idea is to have a word id in several languages, so you only have to
look up the word id and give
Enrico Weigelt wrote:
>
> Hi folks,
>
> I'd like to write an update rule, which touches the a mtime field
> (=current_timestamp) on normal update - when the mtime field isnt
> explicitly set. If the update query explictly sets an mtime value,
> this value has to be let through.
>
> my tables loo
Hi folks,
I'd like to write an update rule, which touches the a mtime field
(=current_timestamp) on normal update - when the mtime field isnt
explicitly set. If the update query explictly sets an mtime value,
this value has to be let through.
my tables look like:
-- base class
CREATE T
Ralph Graulich wrote:
>
> Hello everyone,
>
> given is a table with a version history kind of thing I am currently
> working on. Upon this table there is a view and the application interacts
> with the view only, updating/inserting/deleting is controlled by rules. It
> seems like the record set "
Hello everyone,
given is a table with a version history kind of thing I am currently
working on. Upon this table there is a view and the application interacts
with the view only, updating/inserting/deleting is controlled by rules. It
seems like the record set "OLD" gets changed when it is used i
On Wed, 27 Oct 2004, Dmitry P. Ovechkin wrote:
> Hello.
> I'mtrying to implement history tables using rules.
> I have
> test_table
> --
> create sequence history_seq start 1;
> create sequence test_sequence;
> # source table
> drop table test_table;
> create table test_table (
> i integer
Hello.
I'mtrying to implement history tables using rules.
I have
test_table
--
create sequence history_seq start 1;
create sequence test_sequence;
# source table
drop table test_table;
create table test_table (
i integer default nextval('test_sequence'),
c character(10)
);
# history table
c
El Mar 13 Ene 2004 18:07, Jeff Boes escribió:
>JB: Here's the setup: I wanted to write a rule that would fire on an update
>JB: to one table, and do an update to another table, followed by a notify.
>JB: My first attempt wasn't acceptable to PG (7.3.4):
>JB:
>JB: create rule "my_rule" as
>JB: on up
Here's the setup: I wanted to write a rule that would fire on an update
to one table, and do an update to another table, followed by a notify.
My first attempt wasn't acceptable to PG (7.3.4):
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
(update table_B ...;
I think, your example would work if you replaced the new.id in the rule
with curval ('main_id_seq');
... but see Tom's earlier reply - this is still not a very good thing to
do... For example, it won't work if you try to insert into main anything
with explicitly specified id (not generated by th
> Nevermind- that doesn't work either! Here's the new sample code:
Rules are triggered before the event. You must do it in AFTER trigger.
regards,
bhuvaneswaran
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
paul cannon <[EMAIL PROTECTED]> writes:
> I'm having trouble understanding the behavior of rules with regards to
> default values.
> ...
> If I remove the REFERENCES constraint, then I can see why. The insert
> made into main behaves as expected; it gets nextval('main_id_seq'),
> which comes out to
On Tue, Jul 22, 2003 at 07:47:00PM -0600, paul cannon wrote:
> Until then, I'll have to make a function to do nextval('main_id_seq')
> with every insert, and have the primary key be INTEGER.
Nevermind- that doesn't work either! Here's the new sample code:
-- Begin demo SQL
CREATE SEQUENCE main_i
'Sup list-
I'm having trouble understanding the behavior of rules with regards to
default values.
Here's my situation: I have a table with a column referencing another.
When inserts are made to the second, I would like a certain
corresponding insert made to the first. Here's the simplest case I c
Ok, I found the documentation on using views to udpate the underlying
database. But I can't seem to get my head around making it actually
work, because updates may change only a few columns, and the columns
in my views come from multiple tables.
Could someone provide an example (CC'ing me, please,
Edoardo Causarano <[EMAIL PROTECTED]> writes:
> Hello, I need help on this rule:
> CREATE RULE rule AS ON INSERT TO table WHERE (sum(new."field") > 10) DO
> INSTEAD NOTHING;
> Creation is ok but in inserting a row I get the following from pgaccessII
> number -2147467259
> ERROR: fireRIRrules: fail
Hello, I need help on this rule:
CREATE RULE rule AS ON INSERT TO table WHERE (sum(new."field") > 10) DO
INSTEAD NOTHING;
Creation is ok but in inserting a row I get the following from pgaccessII
number -2147467259
ERROR: fireRIRrules: failed to remove aggs from qual...
Removing the INSTEAD from
Kristian Eide wrote:
>
> I have a table with a reference constraint and an ON DELETE SET NULL action.
> When this action triggers, I also want to update another field in the table,
> actually a timestamp which should be set to NOW().
>
> After reading some documentation it would seem a rule is t
>> ERROR: query rewritten 10 times, may contain cycles
>> It would seem that my WHERE clause is not checked before the action is run.
>No, the WHERE clause is essentially transformed into part of the rule
>query. You can't ever write a rule of the form
> ON UPDATE TO b DO UPDATE b ...
>bec
"Kristian Eide" <[EMAIL PROTECTED]> writes:
> ERROR: query rewritten 10 times, may contain cycles
> It would seem that my WHERE clause is not checked before the action is run.
No, the WHERE clause is essentially transformed into part of the rule
query. You can't ever write a rule of the form
I have a table with a reference constraint and an ON DELETE SET NULL action.
When this action triggers, I also want to update another field in the table,
actually a timestamp which should be set to NOW().
After reading some documentation it would seem a rule is the easiest way to
accomplish this.
Svenn Helge Grindhaug <[EMAIL PROTECTED]> writes:
> create rule a_delete as
> on delete to a do
> delete from b where id1 = old.id1;
> create rule c_delete as
> on delete to c do
> delete from a where id1 = (select id1 from b where id2 = old.id2);
The a_delete rule is run before the actual "DELE
I have a view over a join of tables and when it's performed over the
view a delete i want to delete records in two different tables. The code
that i wrote was:
CREATE RULE "deletetables" AS ON DELETE TO "tables"
DO INSTEAD (
DELETE FROM table2
WHERE id = OLD.
Saluton,
I am using
,
| SELECT version();
| version
|
| PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`
, and upgrading to 7.1 is not possible (it was hard
"Creager, Robert S" <[EMAIL PROTECTED]> writes:
> You indicate trigger, rather than rule. Going by Momjian's book, he
> indicates that rules are "...ideal for when the action affects other
> tables." Can you clarify why you would use a trigger for this?
Primarily because there's a need to get a
due
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] Rule/currval() issue
>
>
> Tim Perdue <[EMAIL PROTECTED]> writes:
> > This is related to the plpgsql project I was working on
> this morning. I'm
> > trying to create a rule, so that when a row is inser
Tim Perdue <[EMAIL PROTECTED]> writes:
>> You probably should be using a trigger, not a rule at all.
> OK - so another rule like this one, is probably ill-advised as well? It seems
> a lot easier than going into the triggers:
> CREATE RULE forum_delete_agg AS
> ON DELETE TO forum
> DO UP
On Wed, Mar 14, 2001 at 01:09:18PM -0500, Tom Lane wrote:
> Tim Perdue <[EMAIL PROTECTED]> writes:
> > This is related to the plpgsql project I was working on this morning. I'm
> > trying to create a rule, so that when a row is inserted into a certain table,
> > we also create a row over in a "cou
Tim Perdue <[EMAIL PROTECTED]> writes:
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (
Entirely untested, but how about replacing currval() in your first try
with nextval()? My theory is that the compilation of the function is
happening before the INSERT happens; therefore the sequence hasn't been
incremented yet; therefore there's no currval() for this backend
instance. If you use
This is related to the plpgsql project I was working on this morning. I'm
trying to create a rule, so that when a row is inserted into a certain table,
we also create a row over in a "counter table". The problem lies in getting
the primary key value (from the sequence) so it can be inserted in tha
Kyle wrote:
> The only complication is
> that there are a class of records which the user should be able to view, but not
> modify. For example,
> the employee can create and modify working records as long as the only
> modification to their status
> is to move them on to "open status" (creating
Jan Wieck wrote:
> Tom Lane wrote:
>
> > This might be better done with a trigger than a rule. For one thing,
> > a trigger can easily raise an exception. MHO is that rules are good
> > when you need to update multiple rows in other tables when certain
> > things happen. If you just want to va
Tom Lane wrote:
Kyle <[EMAIL PROTECTED]> writes:
> If someone happens to know the primary key of a record they should
not be
> able to access, and they try to update it, I would like the backend
to
> ignore the query (or better yet, raise an exception but I haven't
figured
> out how to do that).
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I haven't figured
> > out how to
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I haven't figured
> > out how to d
Jan Wieck wrote:
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > ERROR: Cannot update a view without an appropriate rule.
>
> 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> for a view. What do you think was happening on your old database
> when the "where old.status =
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > ERROR: Cannot update a view without an appropriate rule.
>
> 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> for a view. What do you think was happening on your old database
> when the "where old.status = 'appr'" clause was
Kyle <[EMAIL PROTECTED]> writes:
> ERROR: Cannot update a view without an appropriate rule.
7.1 insists that you provide an *unconditional* DO INSTEAD rule
for a view. What do you think was happening on your old database
when the "where old.status = 'appr'" clause wasn't satisfied?
Nothing good
I have a number of views that seemed to work fine in 7.0.3. When
I try to do an update to these views under 7.1, I get the following
error:
ERROR: Cannot update a view without an appropriate rule.
For example, there's a view that looks like this:
create view pay_req_v_prl as select empl_id,wdat
66 matches
Mail list logo
