[SQL] What's wrong with COPY rights in 7.3.4?
Hi, I tried to use the COPY function in v7.3.4 but I have a problem. According to the documentation, anyboy having the good rights on the table he want to copy could do it without any problem. When I tried it, I found that using this command is impossible if you aren't an administrator for the instance of PostgreSQL. Is it a bug or a *feature*? The commands I tried are the followings: COPY table FROM '/file.sql'; and COPY table TO '/file.sql'; Thanks for responding. -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Create Trigger porting problem from Oracle to PostgreSQL7.3.2
Hello I've got a problem in porting a trigger from Oracle into PostgreSQL. In Oracle you can choose between UPDATE and UPDATE OF COLUMN ... Is there a way to have the same functionality in PostgreSQL? Hope anyone can give me an advice. Doris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Create Trigger porting problem from Oracle to PostgreSQL7.3.2
Hi, I ran into the same thing last week. There is no way to specify that functionality in the trigger creation for Postgresql. You will have to code the trigger to detect when the specific field is updated. Tom Lane had suggested something for me last week. Check out the archive for the mailing list at http://archives.postgresql.org/pgsql-sql/2003-11/index.php and scroll down to November 26. Perhaps Tom will have more to say on this, he's pretty good at this stuff. On another note have you converted any instead of triggers from Oracle to Postgres? I'm not sure how to do that yet, maybe using rules but I'll save that for another message to the mailing list. -Clint Original Message Follows From: Doris Bernloehr <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [SQL] Create Trigger porting problem from Oracle to PostgreSQL7.3.2 Date: Wed, 03 Dec 2003 17:57:51 +0100 MIME-Version: 1.0 Received: from mc10-f9.hotmail.com ([65.54.166.145]) by mc10-s5.hotmail.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 3 Dec 2003 09:12:31 -0800 Received: from hosting.commandprompt.com ([207.173.200.192]) by mc10-f9.hotmail.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 3 Dec 2003 09:11:53 -0800 Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id hB3GwtS23721;Wed, 3 Dec 2003 08:59:18 -0800 Received: from localhost (neptune.hub.org [200.46.204.2])by svr1.postgresql.org (Postfix) with ESMTP id 98A82D1B4AEfor <[EMAIL PROTECTED]>; Wed, 3 Dec 2003 16:58:39 + (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 58440-05 for <[EMAIL PROTECTED]>; Wed, 3 Dec 2003 12:58:08 -0400 (AST) Received: from mail.gmx.net (imap.gmx.net [213.165.64.20])by svr1.postgresql.org (Postfix) with SMTP id 58C07D1D388for <[EMAIL PROTECTED]>; Wed, 3 Dec 2003 12:58:07 -0400 (AST) Received: (qmail 23665 invoked by uid 65534); 3 Dec 2003 16:57:51 - Received: from dsl-213-023-040-229.arcor-ip.net (EHLO gmx.net) (213.23.40.229) by mail.gmx.net (mp001) with SMTP; 03 Dec 2003 17:57:51 +0100 X-Message-Info: U2wzkPk8/jYj5Bpg8rsbCR+dCQ716btYhkbL2fD/ZgI= X-Original-To: [EMAIL PROTECTED] X-Authenticated: #718964 Message-ID: <[EMAIL PROTECTED]> User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en X-Virus-Scanned: by amavisd-new at postgresql.org X-Mailing-List: pgsql-sql Precedence: bulk Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 03 Dec 2003 17:11:53.0858 (UTC) FILETIME=[8C803E20:01C3B9C0] Hello I've got a problem in porting a trigger from Oracle into PostgreSQL. In Oracle you can choose between UPDATE and UPDATE OF COLUMN ... Is there a way to have the same functionality in PostgreSQL? Hope anyone can give me an advice. Doris ---(end of broadcast)--- TIP 8: explain analyze is your friend _ Winterize your home with tips from MSN House & Home. http://special.msn.com/home/warmhome.armx ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Datatype Inet and Searching
Devin Atencio wrote: Dear Users, I am very new to using the inet data type and I have a database with a list of CIDRs in it And I was hoping to be able to do some kind of select statement that would pull the record That the IP given is located in for example. If in my database I have a record like: Id cidr name 1 10.0.0.0/30user1 2 10.0.0.4/30user2 3 10.0.0.8/30user3 I was hoping to be able to do something like: Select * from mytable where cidr <<=’10.0.0.5’ select * from mytable where cider >>= '10.0.0.5' The operation a >>= b mean: a contain or is equal b. Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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. _ Take advantage of our best MSN Dial-up offer of the year six months @$9.95/month. Sign up now! http://join.msn.com/?page=dept/dialup ---(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
