Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
>
> I have some instead of triggers in Oracle, some update instead of triggers
> and some insert instead of triggers. I was thinking that I could maybe use
> instead of rules in PostgreSQL to get the same effect. I converted the
> instead of trigger in Oracle into a PostgreSQL function below:
> CREATE OR REPLACE FUNCTION t_vproduct()
> RETURNS VOID AS '
>DECLARE
> v_productsetno numeric;
> v_productno numeric;
> v_prodqty numeric;
> v_setqty numeric;
> oldqoh numeric;
> newqoh numeric;
>
> --cursor to to get set number, sub-product_no and their quantities in
> the productset
> prodset_cur CURSOR IS
> SELECT productset_no, product_no, prod_qty
> FROM productset
> WHERE productset_no = old.product_no;
>
>BEGIN
> oldqoh := old.qoh;
> newqoh := new.qoh;
>
> --opening and fetching the cursor in the variables
> OPEN prodset_cur;
> FETCH prodset_cur INTO v_productsetno, v_productno, v_prodqty;
>
> --checking if product is a set or individual
> --if it is not a set then update product table
> IF NOT FOUND THEN
> UPDATE product
> SET qoh = qoh - (oldqoh - newqoh)
> WHERE product_no = old.product_no;
> --if it is a SET then
> ELSIF FOUND THEN
> v_setqty := (oldqoh - newqoh); --SET quantity
>
> --loop updates each sub products qoh in the set
> LOOP
> UPDATE product --multiplying quantity of a product in a set
> with quantity of productset, to get total quantity of individual product in
> a set
> SET qoh = qoh - (v_prodqty * v_setqty)
> WHERE product_no = v_productno;
>
> FETCH prodset_cur INTO v_productsetno, v_productno, v_prodqty;
>
> EXIT WHEN NOT FOUND;
> END LOOP;
>
> CLOSE prodset_cur;
> END IF;
>
> RETURN;
>END;
>' LANGUAGE 'plpgsql';
>
> Then my guess for the rule is:
> CREATE OR REPLACE RULE r_vproduct AS ON UPDATE
>TO vproduct DO INSTEAD PERFORM t_vproduct();
>
> I know that function isn't going to work the way I have it. In Oracle that
> function was defined as a trigger:
> CREATE OR REPLACE TRIGGER t_vproduct
> INSTEAD OF UPDATE
> ON v_product
>
> v_product is a view. Getting access to new and old is going to be at least
> one problem I can see. Perhaps I can pass in NEW and OLD into the t_vproduct
> function from the rule (DO INSTEAD PERFORM t_vproduct(new, old);). I'm not
> sure what to do.
>
Not sure if this is of any help ...
AFAIK there are no updatable views in pg.
But aside from that, I cannot see nothing what could not be
done by a pg trigger function:
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
Also try
http://techdocs.postgresql.org/#convertfrom
Converting from other Databases to PostgreSQL
and/or
http://openacs.org/search/search?q=oracle+to+pg+porting&t=Search
HTH
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
Hi Christoph,
Thanks for the links but the techdoc links for converting from Oracle to
PostgreSQL has 2 links that don't go to their intended targets anymore, one
is in the 7.3 docs which is really limited (only covers simple things), and
the Ora2Pg one I don't really get that well.
As far as updateable views, that's why you need instead of triggers. Regular
triggers can't be done on views. So if I make an instead of trigger on a
view that's for updates then I have an updateable view. I figured it out
last night and I was along the right track in my original post with using an
instead of rule to call a function. The trick is that I have to pass in all
the old.col and new.col stuff into the function that I call from the rule.
In Oracle since the instead of stuff is a trigger I had access to the
new.col and old.col stuff. To do it in PostgreSQL rules I had to pass it all
in. I'm going to write a doc about the problems I've encountered during my
conversion project and then submit it to the Postgres people I think (to who
though?). My programming and tech writing background should help I hope.
Thanks for the suggestions Christoph.
-Clint
Original Message Follows
From: Christoph Haller <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
Date: Thu, 04 Dec 2003 17:16:32 MET
Not sure if this is of any help ...
AFAIK there are no updatable views in pg.
But aside from that, I cannot see nothing what could not be
done by a pg trigger function:
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
Also try
http://techdocs.postgresql.org/#convertfrom
Converting from other Databases to PostgreSQL
and/or
http://openacs.org/search/search?q=oracle+to+pg+porting&t=Search
HTH
Regards, Christoph
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
_
Get holiday tips for festive fun.
http://special.msn.com/network/happyholidays.armx
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] How do I convert an interval into integer?
I want to convert an interval (from substraction between two timestamps) into a integer that represents how many seconds that interval has. How do I do that? I am using postgresql 7.3.1 Thanks Wei ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Trigger plpgsql function, how to test if OLD is set?
I have a test I need to do in my trigger function to see if a standard set of shipmentcharges exists, if not I insert two rows. IF (SELECT COUNT(orderchargeid) FROM ordercharges WHERE orderid=NEW.orderid OR orderid=OLD.orderid)=0 THEN I added the "OR orderid=OLD.orderid" expression to handle the case where the orderid is changed. A cascading update causes a duplicate set of shipmentcharges to be added for the shipmentid, since the expression is momentarily true. When this trigger runs on INSERT operations, the OLD variable is not yet set, and the trigger function returns an error. Can anyone suggest a more sensible way to check for OLD before including it in my expression, or another shortcut? Thanks. - CREATE OR REPLACE FUNCTION orders_initordercharges () RETURNS "trigger" AS ' BEGIN -- Check that no ordercharges exist for this orderid IF (SELECT COUNT(orderchargeid) FROM ordercharges WHERE orderid=NEW.orderid OR orderid=OLD.orderid)=0 THEN -- Insert standard initial set of ordercharges INSERT INTO ordercharges (orderid, orderchargecode) VALUES (NEW.orderid,\'SALE\'); INSERT INTO ordercharges (orderid, orderchargecode) VALUES (NEW.orderid,\'S&H\'); END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER orders_initordercharges BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE PROCEDURE orders_initordercharges (); ALTER TABLE ONLY ordercharges ADD CONSTRAINT if_order_exists FOREIGN KEY (orderid) REFERENCES orders(orderid) ON UPDATE CASCADE ON DELETE CASCADE; ---(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] How do I convert an interval into integer?
Dnia 2003-12-04 19:09, Użytkownik Wei Weng napisał: I want to convert an interval (from substraction between two timestamps) into a integer that represents how many seconds that interval has. How do I do that? select extract(epoch from your_interval); Regards, Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Trigger plpgsql function, how to test if OLD is set?
Dnia 2003-12-04 19:18, Użytkownik Jeff Kowalczyk napisał: When this trigger runs on INSERT operations, the OLD variable is not yet set, and the trigger function returns an error. Can anyone suggest a more sensible way to check for OLD before including it in my expression, or another shortcut? Thanks. CREATE OR REPLACE FUNCTION orders_initordercharges () RETURNS "trigger" AS ' BEGIN -- Check that no ordercharges exist for this orderid IF (SELECT COUNT(orderchargeid) FROM ordercharges WHERE orderid=NEW.orderid OR orderid=OLD.orderid)=0 THEN -- Insert standard initial set of ordercharges INSERT INTO ordercharges (orderid, orderchargecode) VALUES (NEW.orderid,\'SALE\'); INSERT INTO ordercharges (orderid, orderchargecode) VALUES (NEW.orderid,\'S&H\'); END IF; RETURN NEW; END; ' LANGUAGE plpgsql; You can always check whether your trigger has been fired as insert or update trigger. DECLARE old_orderid integer; BEGIN if TG_OP=''UPDATE'' then old_orderid=OLD.orderid; else old_orderid=-1; end if; ... Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Trigger plpgsql function, how to test if OLD is set?
Tomasz Myrta wrote: > You can always check whether your trigger has been fired as insert or > update trigger. > DECLARE old_orderid integer; > BEGIN >if TG_OP=''UPDATE'' then > old_orderid=OLD.orderid; >else > old_orderid=-1; >end if; Thank you, that works well enough. I'm beginning to appreciate the complexity of working with both the familiar DRI (contraints) and triggers, which are something I have rarely used. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] ECPG and User-defined Types.
Hi, How can i use a user-defined type in ECPG code ? For example: I have the following stored-procedure: CREATE FUNCTION teste(MY_TYPE) RETURNS boolean; where MY_TYPE is: CREATE TYPE MY_TYPE AS (id_person INT8, id_book INT8); Now, i am having problem with: EXEC SQL SELECT teste(:asd); -- Andre <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How Do I Toggle Quoted Identifiers?
I'm on PHP 4.2.2 and RedHat 9 with PGSQL. I want to turn quoted identifiers off with my SQL queries. What SQL statement or .CONF setting do I need to change so that I can turn quoted identifiers off? Quoted identifiers, as I understand them, are where you must put double quotes around any table or column name in order to preserve its case, or in order to preserve an internal space it may have in the identifier name. I was looking for something like: SET QUOTED_IDENTIFIERS = OFF; SELECT Username FROM Logins; For that matter, how do I turn case-sensitivity off? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Validity check in to_date?
hi,
SELECT isfinite(timestamp '123.45.2003'); if this is true, the date is
ok, if error, than not :)
C.
Alexander M. Pravking wrote:
I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
to_date
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
to_date
2007-01-03
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] XML & Postgres Functions
hi, zerobearing2 wrote: Hi all- I'm migrating to postgres from the MS SQL Server land, as I can see a great potential with postgres, I was wondering if anyone has experimented or started a project with XML inside user defined functions? I've seen the contrib/xml shipped with the distro, as I see it's usefulness, it's not quite what I had in mind (lacks the ablity to return recordset/table of nodes). Also, the project XpSQL seems interesting, but still not it. I'm looking for a solution similar to the OpenXML that MS implements in their SQL Server. A way to parse XML into a table and/or a temp table for use inside a function. For example, I would like to serialize my objects into XML, send them to a postgres function for saving/updating the relational data inside postgres. I envision something of the following could be useful: XML -- ... UDF -- create function somefunc(xmldata text) begin xmldoc := preparedoc(xmldata); update tablename set field1=x.field1, field2=x.field2, from xmltable(xmldoc, '/table/[EMAIL PROTECTED]') as x preform removedoc(xmldoc); end; Check the xml dir under contrib. Or/And http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=utf-8&q=xml+in+postgres+site%3Apostgresql.org C. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] relationship/table design question
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 going to seperate tables? Finally, can I make the reference constraint be 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? 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How Do I Toggle Quoted Identifiers?
On Thursday 04 December 2003 19:42, Google Mike wrote: > I'm on PHP 4.2.2 and RedHat 9 with PGSQL. I want to turn quoted > identifiers off with my SQL queries. What SQL statement or .CONF > setting do I need to change so that I can turn quoted identifiers off? Short answer - you don't. Your understanding is correct, basically PG will lowercase identifiers unless you quote them, in which case you will want to use quotes when accessing them. So... CREATE TABLE AAA ... CREATE TABLE "BBB" ... CREATE TABLE "ccc" ... SELECT * FROM AAA; -- works SELECT * FROM aaa; -- works SELECT * FROM "AAA" -- fails SELECT * FROM BBB; -- fails SELECT * FROM "BBB"; -- works SELECT * FROM CCC; -- works SELECT * FROM "ccc"; -- works SELECT * FROM "CCC"; -- fails So long as you don't create your identifiers with quotes, you can refer to them as upper/lower case. Personally, I create them all lower-case anyway and use caps for SQL keywords. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How Do I Toggle Quoted Identifiers?
Google Mike writes: > I'm on PHP 4.2.2 and RedHat 9 with PGSQL. I want to turn quoted > identifiers off with my SQL queries. What SQL statement or .CONF > setting do I need to change so that I can turn quoted identifiers off? There is no setting for that. > For that matter, how do I turn case-sensitivity off? There is no setting for that either. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
