[SQL] More plsql questions: updates on views
Hi all -
I'm trying to work through using views in order to access multiple
tables while allowing normal operations on them.
To keep things simple, this is a stripped down version of what the
structure for one of the views is like:
create table base (
id serial primary key,
owner int ,
attribute text );
create table specific_1 (
id serial primary key,
base_id int references base,
otherattr text );
create or replace view my_view as (
select base.id, base.owner, base.attribute,
specific_1.other
from base as b, specific_1 as s1
where (b.id = s1.base_id) );
create or replace rule my_view_insert_rule as
on insert to my_view do instead (
insert into base (owner, attribute) values
(new.owner, new.attr);
insert into specific_1 (base_id, otherattribute) values
( ( select currval('base_id_seq') ), new.otherattr ); );
create or replace rule my_view_delete_rule as
on delete to my_view do instead (
delete from base where id = old.id;
delete from base specific_1 where base_id = old.id; );
(BTW, I know there's general concensus that the use of currval in that
insert rule is a bad idea, and I understand why; This application uses
libraries that will not cause grief there.)
Now I need to be able to update it. My initial thought was to create a
function to handle this tripped by an AFTER trigger. I've been reading
up on dynamic execution from functions, but can't see how to access the
SET clause or the WHERE clause. Is this possible?
Is there any other way to get this effect?
Thanks in advance,
-j
--
Jamie Lawrence[EMAIL PROTECTED]
The strength of our liberty depends upon the chaos and
cacophony of the unfettered speech the First Amendment
protects.
- Judge Stewart Dalzell
---(end of broadcast)---
TIP 3: 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] Odd problems with create rule
Hi all - I'm having a strange problem creating a rule on a view. I've done this with no problem in other databases... I'm wondering if somehow there was something omitted or some other type of prolem with the database creation. Or, I could have no idea what I'm talking about. Any guesses? dlm=# create or replace rule addenda_insert_rule as dlm-# on insert to addenda do instead dlm(# insert into documents ( dlm(# project_id, dlm(# doc_num, dlm(# description, dlm(# date, dlm(# people_id, dlm(# parent, dlm(# document_type, dlm(# state, dlm(# machines_id, dlm(# phases_id ) dlm(# values ( dlm(# new.projects._id, dlm(# new.doc_num, dlm(# new.description, dlm(# new.date, dlm(# new.people_id, dlm(# new.parent, dlm(# new.document_type, dlm(# new.state, dlm(# new.machines_id, dlm(# new.phases_id ); ERROR: Namespace "*NEW*" does not exist dlm=# Thanks. -j -- Jamie Lawrence[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Odd problems with create rule
On Fri, 25 Jul 2003, Tomasz Myrta wrote: > Dnia 2003-07-25 23:38, U?ytkownik Jamie Lawrence napisa?: > >dlm(# values ( > >dlm(# new.projects._id, > ^ > >dlm(# new.doc_num, > >dlm(# new.description, > >dlm(# new.date, > >dlm(# new.people_id, > >dlm(# new.parent, > >dlm(# new.document_type, > >dlm(# new.state, > >dlm(# new.machines_id, > >dlm(# new.phases_id ); > >ERROR: Namespace "*NEW*" does not exist > >dlm=# > > Too many dots? > Shouldn't it be "new.project_id"? Oh, god. I'm a moron, or at least should have my vision checked. Thank you. -j > Regards, > Tomasz Myrta -- Jamie Lawrence[EMAIL PROTECTED] Americans will tolerate just about anything as long as you don't stop traffic. - Dan Rather ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Fwd: Bad Join moment - how is this happening?
10 | 2| none 2 |2 | 1| foo (2 rows) dlm=# select * from addenda; id | oid | projects_id | doc_num |description | date | createdate | moddate | people_id | parent | document_type | state | machines_id | phases_id | item_num | drawing_reference ++-+-++---+++---++---+---+-+---+--+--- 10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 2| none 10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 1| foo 2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 2| none 2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 1| foo 10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 2| none 10 | 183371 | 1 | | this is a test description | | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | | | | | | 1| foo 2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 2| none 2 | 180877 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | | | 1 | 1 | | | 1| foo (8 rows) I'm sure I'm being an idiot, but does anyone have a fix? Thanks. -j -- Jamie Lawrence[EMAIL PROTECTED] It it ain't broke, let me have a shot at it. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Fwd: Bad Join moment - how is this happening?
Sorry to be slow on responses... Thanks to everyone who replies. On Wed, 30 Jul 2003, Richard Huxton wrote: > On Wednesday 30 July 2003 21:07, Jamie Lawrence wrote: > > I fully admit that I've been staring at this too long, and simply don't > > understand what is wrong. Apologies aside, any kind sql hackers who care > > to look this over will earn my undying gratitude, and a beer in the bar > > of your choice, should we ever meet. > > I'll take that beer (assuming I'm right) I think you nailed it first. You appear to be in London, which I haven't made it to in ~15 years, and mailing it is probably a bad idea. Ever make it to the NYC area? > > I appear to be getting a cartesean product when I select against the view > > 'addenda', when I want a left inner join. That is, I want documents > > records matched to addenda records only when there is a record in > > d_addenda with a documents_id that matches the id field in documents. > > I think this is the "adding a table into the FROM" feature of PG. You're > referring to documents.xxx in the select and d.id in the FROM. PG tries to > help out by adding the table into the FROM for you - hence cartesian join. > > I think you can turn this "feature" off in the config file in 7.3.x (haven't > checked this though) This was exactly it. Thanks for the help, this was really making me crazy. -j -- Jamie Lawrence[EMAIL PROTECTED] anger, bargaining, depression, and, finally, acceptance ---(end of broadcast)--- TIP 3: 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] Auto-update a field when record is changed
> On Fri, 22 Aug 2003, Stuart wrote: > > > Folks, > > > > I was wandering if there was a feasible way to automatically update a field > > in a table, say 'revision_date' in a record whenever any other field in the > > record is changed. My attempts to use a trigger caused repeating loops and > > bombed with error. I would like to be able to update such a field > > automatically anytime a record was updated. Any help would be appreciated. Unless I'm misunderstanding you, this is really easy. Here's what I use in nearly every database I build: create or replace function timestamp_fn() returns opaque as ' begin NEW.moddate = now(); return NEW; end ' language 'plpgsql'; create table blah ( ... createdate timestamp default now(), moddate timestamp, create trigger blah_timestamp_tr before insert or update on blah for each row execute procedure timestamp_fn(); Make the obvious changes for only doing this on updates. Or am I misunderstanding your goal? -j -- Jamie Lawrence[EMAIL PROTECTED] "One of the great things about books is that sometimes there are some fantastic pictures." - George H. W. Bush ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problems with NEW.* in triggers
Hi folks - I'm having a problem with what looks like it should be trivial. For the function create or replace function timestamp_fn() returns opaque as ' begin NEW.moddate := coalesce(NEW.moddate, now()); return NEW; end ' language 'plpgsql'; on an after insert or update trigger, never sets moddate to now(). I had thought that if moddate isn't included in an insert or update, that it would be null in the NEW context, so that this would fall through to the now() call. (I also tried this as below: create or replace function timestamp_fn() returns opaque as ' begin if NEW.moddate is not null then return NEW; else NEW.moddate := now(); return NEW; end if; end ' language 'plpgsql'; With the same results.) Any thoughts on what I'm doing wrong?? -j -- Jamie Lawrence[EMAIL PROTECTED] Never eat anything bigger than your head. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems with NEW.* in triggers
On Tue, 04 Nov 2003, Tom Lane wrote:
> Jamie Lawrence <[EMAIL PROTECTED]> writes:
> > I had thought that if moddate isn't included in an insert or update,
> > that it would be null in the NEW context,
>
> No, it would be whatever the value to be assigned to the column would
> be, if the trigger were not present. In particular, during an UPDATE
> it's going to contain the old value of the field. In an INSERT it would
> be whatever the column's default value is.
For an insert, the default is null in this case.
test=# create table trash (moddate timestamp, message text);
CREATE TABLE
test=# create or replace function timestamp_test() returns opaque as '
test'# begin
test'# NEW.moddate := coalesce(NEW.moddate, now());
test'# return NEW;
test'# end
test'# ' language 'plpgsql';
CREATE FUNCTION
test=# create trigger critter_timestamp_test after insert or update on critter for
each row execute procedure timestamp_fn();
CREATE TRIGGER
test=# insert into trash (message) values ('hi there');
INSERT 560920 1
test=# insert into trash (message) values ('hi there');
INSERT 560921 1
test=# select * from trash;
moddate | message
-+--
| hi there
| hi there
(2 rows)
test=#
I don't understand why moddate isn't getting set to now() in the above.
(Point taken on updates... I was thinking about NEW in slightly
the wrong way for an after trigger.)
> I am not sure what your intention is here. If you want the trigger to
> force the field to current time, it can certainly do that. If you want
> the user to control whether the field is updated, why do you need a
> trigger at all?
Excellent question, sigh. I'm trying to bandaid a bad design choice
until the application can be changed.
Now that you have me thinking about it, an update rule is probably a
better idea.
Thanks for the help, I appreciate it -
-j
--
Jamie Lawrence[EMAIL PROTECTED]
"Remember, half-measures can be very effective if all you deal with are
half-wits."
- Chris Klein
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems with NEW.* in triggers
On Tue, 04 Nov 2003, Tom Lane wrote: > Jamie Lawrence <[EMAIL PROTECTED]> writes: > > I don't understand why moddate isn't getting set to now() in the above. > > Josh fingered the problem there --- you need a BEFORE trigger if you > want to affect the data that will be stored. I had missed that little > mistake :-( Yep. Many thanks to both of you. -j -- Jamie Lawrence[EMAIL PROTECTED] If built in great numbers, motels will be used for nothing but illegal purposes. - J. Edgar Hoover ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Updating session id based on accesstimeout
On Sat, 29 Nov 2003, Andreas Heissenberger wrote: > Hi, > > I want to set a session id based on a session timeout per user. I have a very large > logfile with more than 1.3 Mio records and look for a very efficient solution - > maybe with PL/pgSQL . > > The important columns in the table are: access timestamp and userid sorted by userid > and access time. > The logic for the session id is: when ever the userid changes or the time between > two following records is greater than 60 sec create a new session id and update the > record with this id. > > I tried to solve this problem with Perl and the DBI interface - but it took about 12 > hours to update all records. > The fastes solution I found until now was not to use any data base, sort the data > with the unix command sort and process the logfile text directly with perl. You won't find a method faster than updating text files, until you stop brute forcing the data. Databases don't provide speed, at least in this case. They provide ACID compliance, and other features. Perhaps, if you revisit your assumptions, you might find that your need is solved by a different notion of the problem. -j -- Jamie Lawrence[EMAIL PROTECTED] "God created the integers, all else is the work of man." - Kronecker ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] relationship/table design question
On Tue, 02 Dec 2003, Taylor Lewick wrote: > Hi all, new to postgresql and I have a question about how to lay out my > database. > > I have a database with 4 main tables, organizations, contacts, events, > grants. My thinking here is that organizations can have contacts, sponsor > events, > and sponsor grants, so it is what I would call > the main table. I know how to set up the other tables so that they have a > reference back to org_id. (Basically a foreign key). > > How would I handle it (set up in SQL) if I want to be able to assign a > contact to an event. In the event table, would I make a reference back to > the contact table? Can I have more than one foreign key reference per table Probably not what you want to do. > going to seperate tables? Finally, can I make the reference constraint be No, but see below. (Well, you could using an array or a marshalled field, but I can't imagine why you'd want to.) > able to be NULL. Meaning, can I make it so that an event can, but doesn't > have to have a contact assigned to it? Yes. > The tables (a simplified view) would look like this... > > Organization Table: > org_id > org_name > > Contact Table: > contact_id > contact_name > > Event Table: > event_id > event_name > reference back to org_id in org table > reference back to contact_id in contact_table but there doesn't have to be a > contact associated with this event. Hi there - In general, I would do something along these lines: create table organizations ( id serial primary key, organization text ); create table contacts ( id serial primary key, name text ); create table events ( id serial primary key, name text); create table events_contacts ( id serial primary key, events_id int references events, contacts_id int references contacts ); In order to associate a contact with an event, you insert a row in events_contacts with the appropriate ids of the event and the contact. Some people call this a join table, others a mapping table, others [insert 15 other names for many to many relations]. You'll have to decide how updates and deletes should behave for your purposes, and add in those clauses to the foreign key declarations. HTH, -j -- Jamie Lawrence[EMAIL PROTECTED] The bureaucracy is expanding to meet the needs of an expanding bureaucracy. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Different encodings in different DBs in same cluster
Hi All, I was going through the docs for Postgres 8 for info on setting the character set (to UTF8). In the docs here: http://www.postgresql.org/docs/8.0/interactive/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED I see: Since these locale settings are frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real. It is likely that these mechanisms will be revisited in future versions of PostgreSQL. One way to use multiple encodings safely is to set the locale to C or POSIX during initdb, thus disabling any real locale awareness. Does anyone know what "more theoretical than real" mean in this context? If I set the locale to C, is it going to work correctly with UTF8 encoded data? Thanks, -j -- Jamie Lawrence[EMAIL PROTECTED] It's strange to hear people like Gordon Liddy talking about morality. He hasn't been out of jail all that long. - Ben Bradlee ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
