Rule problem - PostgreSQL Database

Kiarash Bodouhi

Hello all

I tried to use following rule instead of using a default value(I want to be
able to put a function instead of the constant value later on). But it keeps
giving me infinite recursion error. Any ideas?

CREATE OR REPLACE RULE "INSERT" AS

ON INSERT TO customer_rate where new.code is null DO INSTEAD INSERT INTO
customer_rate (prefix, rate, name, code,groupid)

VALUES (new.prefix, new.rate, new.name,'1',ne w.groupid);

Thanking you in advance

Regards

Kia

Nov 23 '05 #1

Subscribe Reply

1602

Tom Lane

"Kiarash Bodouhi" <kb******@yahoo .com> writes:

I tried to use following rule instead of using a default value
You can't ... at least not that way.
(I want to be
able to put a function instead of the constant value later on).

There's nothing stopping you from putting a function into the DEFAULT
clause.

A "BEFORE INSERT" trigger is another possible approach.

Novices often think that rules are simpler to use than triggers,
but in my experience the opposite is true. Triggers have some
notational ugliness but the conceptual model is simple ... for
rules the notation looks easy but wrapping your head around what
will really happen is another story.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

Kiarash Bodouhi

Thanks for the reply. I initially wanted to put the function as the default
clause. But the problem is that the function should receive one of the other
fields as the argument. Is there anyway to do that? I guess as you said I
have to use triggers.

-----Original Message-----
From: Tom Lane [mailto:tg*@sss. pgh.pa.us]
Sent: Sunday, October 10, 2004 4:53 PM
To: Kiarash Bodouhi
Cc: pg***********@p ostgresql.org
Subject: Re: [GENERAL] Rule problem

"Kiarash Bodouhi" <kb******@yahoo .com> writes:

I tried to use following rule instead of using a default value
You can't ... at least not that way.
(I want to be
able to put a function instead of the constant value later on).

There's nothing stopping you from putting a function into the DEFAULT
clause.

A "BEFORE INSERT" trigger is another possible approach.

Novices often think that rules are simpler to use than triggers,
but in my experience the opposite is true. Triggers have some
notational ugliness but the conceptual model is simple ... for
rules the notation looks easy but wrapping your head around what
will really happen is another story.

regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3

Similar topics

3152

problem w/CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE w/multiple inserts

by: jtocci | last post by:

I'm having a big problem with CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50) records based on a single INSERT to a view. Either I get a 'too much data for field' or the query just runs on and on til I have to restart the postmaster. I have found rules to compare mine to but people limit the resulting insert to one record (the WHERE generally limits the result of the SELECT to one...

PostgreSQL Database

3580

error when creating rule

by: Barbara Lindsey | last post by:

Thank you for your help on the trigger question. The RULE worked for most of the cases I had for this, but I have one that is giving me trouble. Here are my table definitions: CREATE SEQUENCE "stat_id_seq" cache 1; CREATE TABLE "ref_status" ( "status_id" integer DEFAULT nextval('stat_id_seq') PRIMARY KEY, "short_name" varchar(5), "description" varchar(25), "modified" timestamp with time zone DEFAULT current_timestamp,

PostgreSQL Database

2578

CREATE RULE problem/question requesting workaround

by: Chris Travers | last post by:

Hi all; I am using PostgreSQL 7.4 on RedHat 9, though I don't think that is important to this problem. I am attempting to write a rule that will check to see whether certain conditions are right for a SELECT query and abort the query of those connections are not right. In this case, I am trying to do additional permissions enforcement. I have written a function to do the checking, but when I create the rule, I get an error. So here...

PostgreSQL Database

2693

Validation rule trouble - ensuring values held in one field are less than those held in another field

by: Joey P | last post by:

Hi all, I am doing a project for university whereby i have to implement a simple database related to a frozen foods company. I am having some trouble though creating a validation rule for one of my fields. I have a table called "Product" and two of the fields included in this table are "Cost Price" and "Retail Price". I need to create a validation rule so that the Cost Price is always less than the Retail Price. I have tried...

Microsoft Access / VBA

3158

Verifiable methods and C# language rule about local variable

by: Edward Yang | last post by:

A few days ago I started a thread "I think C# is forcing us to write more (redundant) code" and got many replies (more than what I had expected). But after reading all the replies I think my question about local variable initialization is still not solved. And some of the replies forked into talking about out parameters. And the thread is becoming way too deep. So I open a new thread here. My question in the previous thead has turned...

C# / C Sharp

1741

Rule uses wrong value

by: Jeff Boes | last post by:

(I thought I posted this yesterday from Google Groups, but it doesn't appear to have "taken".) I'm having a problem with a rule designed to log new rows inserted into one table. The base table is very volatile; rows are inserted from various places, including both application code and triggers. Then they are read out by another application (the table is used as a job queue) and deleted. My rule attempts to re-record the rows in another...

PostgreSQL Database

4003

Temporarily disable rule, is this possible?

by: Net Virtual Mailing Lists | last post by:

Hello, I have a table with a rule that goes something like this: CREATE OR REPLACE RULE sometable_delete ON DELETE DO delete FROM cache WHERE tablename='sometable'; CREATE OR REPLACE RULE sometable_insert ON INSERT DO delete FROM cache WHERE tablename='sometable'; CREATE OR REPLACE RULE sometable_update ON UPDATE DO delete FROM cache WHERE tablename='sometable';

PostgreSQL Database

2082

"Three Strikes You're Out" rule fails

by: comp.lang.php | last post by:

I have a counter that evokes the "Three Strikes You're Out" rule.. if you make more than N mistakes it auto-resets to avoid flooding $_SESSION with attempt after attempt, etc. However, the counter never advances beyond 1! // HANDLE THE PART WHERE THE STUDENT INFORMATION WILL BE UPDATED OR SEARCHED if (is_array($_POST) && @sizeof($_POST) > 0) {

PHP

2052

Reason behind containing block rule

by: Bill Norton | last post by:

If you wrap one division around another like this: <div id="wrapper"> <div id="child">...</div> </div> ....the wrapper division will be considered the containing block of the child division in all cases except where 1. the positioning of the wrapper is static and 2. the positioning of the child is absolute.

HTML / CSS

9687

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10485

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10252

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10231

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9073

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7565

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6805

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5585

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2938

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General