Re: [SQL] Conditional rule?
> > CREATE RULE newsrule AS ON INSERT TO news
> > WHERE new.publishtime NOTNULL DO
> > INSERT INTO news_unpublished VALUES (new.id);
>
> The following happens:
> rules=# insert into news (title, time) values('Hei', now());
> ERROR: referential integrity violation - key referenced from
> news_unpublished not found in news
I noticed this, too.
More generally, I've found that whereas rules initially looked like a
great thing to use, in practice I have really no idea what they're
going to do. And I find their description in the programmer's guide
confusing. Is there any better rules tutorial out there?
-itai
[SQL] Transactions
Does postgreSQL support nested transactions?
Re: [SQL] Conditional rule?
- Original Message -
> No. The rule
>
> CREATE RULE newsrule AS ON INSERT TO news
> WHERE new.publishtime NOTNULL DO
> INSERT INTO news_unpublished VALUES (new.id);
>
> should do the job perfectly. Maybe you want to have the
> following rules too:
The following happens:
rules=# insert into news (title, time) values('Hei', now());
ERROR: referential integrity violation - key referenced from
news_unpublished not found in news
Seems the data is inserted into news_unpublished first, thereby violating
the constraint I have defined for the news_id field (see below). After
removing the constraint a second problem arose; the id created for news
(serial) was 4, while the id inserted into news_unpublished was 3. So far a
trigger procedure seems to be the best solution.
> CREATE RULE newsrule4 AS ON DELETE TO news
> WHERE old.publishtime NOTNULL DO
> DELETE FROM news_unpublished WHERE news_unpublished.id =
old.id;
This is also achieved by the following right? (id is the primary key for
news):
create table news_unpublished (
news_id int references news on delete cascade
);
André Næss
Re: [SQL] BLOBs
- Original Message - From: "Bernie Huang" <[EMAIL PROTECTED]> > Hi, everyone, > > Browsing through the online manual, I didn't find anything related to > BLOBs, but I know there must be BLOBs since many people are asking about > it on the list and there is a pg_fetch_object() in PHP for Postgres. > Could anyone points me an URL where I can look it up? Thanks. PHP's pg_fetch_object() actually just turns a normal row into an object using the fieldnames as names for the instance variables. I couldn't find anything about BLOBs in the PostgreSQL reference, but have a look at the pg_lo() functions in PHP, they are used for interacting with BLOBs André Næss
Re: [SQL] Conditional rule?
Thanks for all the help so far. What I now have is the following structure: create table b_news ( id serial primary key, title varchar(60), time timestamp ); create table b_news_unpublished ( news_id int references news on delete cascade ); CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS ' BEGIN IF TG_OP = ''INSERT'' THEN IF NEW.time NOTNULL THEN INSERT INTO b_news_unpublished VALUES (NEW.id); END IF; END IF; IF TG_OP = ''UPDATE'' THEN IF NEW.time NOTNULL AND OLD.time ISNULL THEN INSERT INTO b_news_unpublished VALUES (NEW.id); END IF; IF NEW.time ISNULL AND OLD.time NOTNULL THEN DELETE FROM b_news_unpublished WHERE news_id=NEW.id; END IF; END IF; RETURN null; END; ' LANGUAGE 'plpgsql'; create trigger b_news_trigger after insert or update on b_news for each row execute procedure b_news_trigproc(); And this works as intended. There are however a few things that worries me. First of all, I can't seem to find any way to list the trigger and the function, they seem invisible. This is problematic because my work will be continued by others, and allthough I will document everything I think it should be possible to see the triggers and functions somehow... Secondly, I miss one final idea, when a delete is performed on the b_news_unpublished table, I would like to set up a rule or procedure that sets the time value to null in b_news for each row that is affected by the delete. I understand that the OLD and NEW objects are accessible only during UPDATE or INSERT operations, so I can't quite see how to do this... I also find it rather inelegant to use the constraint to handle DELETE operations on news, whereas UPDATEs and INSERTs are handled by the trigger procedure. Somehow I would like to either do all the tasks using the trigger procedure, or using rules. As for Itai Zukerman's comment: AOL. Good resources around triggers and rules are very much needed! Regards André Næss
Re: [SQL] Conditional rule?
"=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <[EMAIL PROTECTED]> writes: > Secondly, I miss one final idea, when a delete is performed on the > b_news_unpublished table, I would like to set up a rule or procedure that > sets the time value to null in b_news for each row that is affected by the > delete. I understand that the OLD and NEW objects are accessible only during > UPDATE or INSERT operations, so I can't quite see how to do this... I might be wrong, but I thought the way it works is: INSERT: "NEW" refers to to-be-inserted row UPDATE: "OLD" is old row state, "NEW" is planned new row state DELETE: "OLD" holds row to be deleted regards, tom lane
RE: RE: Re(2): [SQL] optimize sql
I'm kind of new to pgsql, but as long as MAX works for boolean fields, they you can just change the first query below with: HAVING MAX(active) != 't' but it seems that pgsql can have user defined aggregates, so you could define a function that computes the MAX of a boolean and define true to be the max. Henry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 27, 2000 9:28 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: RE: Re(2): [SQL] optimize sql but active is a boolean field. [EMAIL PROTECTED] writes: >If you know that 't' will always be the highest character in the active >field for all records: > >SELECT name FROM office, office_application >WHERE code = office_code >GROUP BY name >HAVING MAX(active) < 't' > >Of course, if you have an active that is 'z' for example, then this won't >work. I think this should work also regardless of max(active) for the >table: > >SELECT name FROM office, office_application >WHERE code = office_code AND active <= 't' >GROUP BY name >HAVING MAX(active) < 't' >UNION >SELECT name FROM office, office_application >WHERE code = office_code AND active >= 't' >GROUP BY name >HAVING MIN(active) > 't' > >Henry > > >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, July 26, 2000 9:40 PM >To: [EMAIL PROTECTED]; [EMAIL PROTECTED] >Subject: Re(2): [SQL] optimize sql > > >[EMAIL PROTECTED] writes: >>How does the output of the above differ from: >> >>SELECT name FROM office, office_application >>WHERE code = office_code >>AND active != 't'; >> >>Without knowing the table structures (which tables to active, code, >>and office_code belong to?) it's hard to suggest much else. >> >>Ross > > >The name and code fields belong to office table. While >office_code and active fields belong to office_application table. >The name field have duplicates and among the duplicates, >only one active field is TRUE. I just wanted to get name field >that has no TRUE active field. Any other idea? Thanks. > >sherwin
[SQL] two table comparison: find a mismatch?
Hello,
here is to table:
Table "city"
Attribute|Type | Modifier
+-+-
city_id| smallint| not null default
nextval('seq_city'::text)
region_id | smallint|
region_tour_id | smallint|
country_id | smallint|
name_fr| varchar(40) |
name | varchar(40) |
url_id | smallint|
Table "region"
Attribute |Type | Modifier
---+-+--
region_id | smallint| not null default nextval('seq_region'::text)
country_id| smallint|
name_fr | varchar(40) |
name | varchar(40) |
address | boolean |
url_id| smallint|
I whant to fins a "hole" in the linkage... a region number in the city
table is not in the region table any more.
How can I do that?
Where is the city.region_id <> region.region_id
Thanks!
--
~
Marc Andre Paquin
Re: [SQL] two table comparison: find a mismatch?
On Fri, Jul 28, 2000 at 04:54:58PM -0400, Web Manager wrote:
> Hello,
>
> here is to table:
>Table "city"
>Attribute|Type | Modifier
> +-+-
> city_id| smallint| not null default
> nextval('seq_city'::text)
> region_id | smallint|
> region_tour_id | smallint|
> country_id | smallint|
> name_fr| varchar(40) |
> name | varchar(40) |
> url_id | smallint|
>
>
> Table "region"
> Attribute |Type | Modifier
> ---+-+--
> region_id | smallint| not null default nextval('seq_region'::text)
> country_id| smallint|
> name_fr | varchar(40) |
> name | varchar(40) |
> address | boolean |
> url_id| smallint|
>
>
> I whant to fins a "hole" in the linkage... a region number in the city
> table is not in the region table any more.
>
> How can I do that?
>
SELECT * FROM city WHERE region_id NOT IN (SELECT region_id FROM region)
should do it.
BTW, the syntax on your defaults for the id's seems a bit odd. Any reason
your casting the text literal to text? Have we got a bug somewhere?
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [SQL] Transactions
On Fri, 28 Jul 2000, Carolyn Lu Wong wrote: > Does postgreSQL support nested transactions? > no.
Re: [SQL] Conditional rule?
On Fri, 28 Jul 2000, André Næss wrote: > Thanks for all the help so far. What I now have is the following structure: > > create table b_news ( > id serial primary key, > title varchar(60), > time timestamp > ); > > create table b_news_unpublished ( > news_id int references news on delete cascade > ); > > CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS ' > BEGIN > IF TG_OP = ''INSERT'' THEN > IF NEW.time NOTNULL THEN > INSERT INTO b_news_unpublished VALUES (NEW.id); > END IF; > END IF; > IF TG_OP = ''UPDATE'' THEN > IF NEW.time NOTNULL AND OLD.time ISNULL THEN > INSERT INTO b_news_unpublished VALUES (NEW.id); > END IF; > IF NEW.time ISNULL AND OLD.time NOTNULL THEN > DELETE FROM b_news_unpublished WHERE news_id=NEW.id; > END IF; > END IF; > RETURN null; > END; > ' LANGUAGE 'plpgsql'; > > create trigger b_news_trigger > after insert or update on b_news > for each row execute procedure b_news_trigproc(); > > And this works as intended. There are however a few things that worries me. > First of all, I can't seem to find any way to list the trigger and the > function, they seem invisible. This is problematic because my work will be > continued by others, and allthough I will document everything I think it > should be possible to see the triggers and functions somehow... > > Secondly, I miss one final idea, when a delete is performed on the > b_news_unpublished table, I would like to set up a rule or procedure that > sets the time value to null in b_news for each row that is affected by the > delete. I understand that the OLD and NEW objects are accessible only during > UPDATE or INSERT operations, so I can't quite see how to do this... OLD.* is available during TG_OP = ''DELETE'' in a trigger. There are sometimes some referential integrity problems on DELETE when using triggers to do things on tables that have a RI relationship. Like, if you have a table that REFERENCES another table ON DELETE SET NULL, and there is a DELETE proc on the referenced table that also does some other update on those records, then the regular trigger might happen before the contraint trigger. In this case, an update would have a RI problem when it updates some attribute and the contraint is checked again - it would fail since the referenced primary key is deleted already but foreign key has not been SET NULL yet by the contraint trigger. I'm not sure what the rules are on the order of contraint trigger and other trigger execution but sometimes the order isn't what you want and then you get the problem on delete. If it happens, like it happened to me, you might quit using foreign keys and just program your triggers as much as you can to do the same checks that the contraint triggers are doing but by having your own triggers do it, you have control of the order of how things happen on delete. > > I also find it rather inelegant to use the constraint to handle DELETE > operations on news, whereas UPDATEs and INSERTs are handled by the trigger > procedure. Somehow I would like to either do all the tasks using the trigger > procedure, or using rules. > > As for Itai Zukerman's comment: AOL. Good resources around triggers and > rules are very much needed! > > Regards > André Næss -- - Robert
