Re: [SQL] UPDATE WITH ORDER BY
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
(Please send email as text, not HTML)
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description
> ...
BEGIN;
CREATE SEQUENCE fruit_seq;
CREATE TABLE newfruit AS SELECT nextval('fruit_seq')::int AS newid, * FROM
fruit ORDER BY lower(description);
ALTER TABLE newfruit DROP COLUMN id;
ALTER TABLE newfruit RENAME COLUMN newid TO id;
DROP TABLE fruit;
ALTER TABLE newfruit RENAME TO fruit;
DROP SEQUENCE fruit_seq;
COMMIT;
SELECT * FROM fruit ORDER BY id ASC;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200504270805
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn
VWsBmACCUFIdzRDRRalG6KI=
=y3G9
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] trigger/rule question
Hi folks, for database synchronization I'm maintaining an mtime field in each record and I'd like to get it updated automatically on normal writes (insert seems trivial, but update not), but it must remain untouched when data is coming in from another node (to prevent sync loops). I first tried it with rules on update, but I didnt find any trick to prevent infinite recoursion. If I'd replace update by delete and reinsert, I'll probably run into trouble with constaints and delete rules. Triggers dont seem to have this problem, but require an function call per record, while a rule solution would only rewrite the actual query. But still I've got the unsolved problem, how to decide when to touch the mtime and when to pass it untouched. I didnt find any trick to explicitly bypass specific triggers yet. Any ideas ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] SYNTAX ERROR ON FOR... LOOP
Hi Guys, I am having a "simple syntax problem" but very strange... I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I am getting syntax error on this contol structure... If I comment the IF / ELSE / ENDIF the fuction works ... I am using PostgreSQL win 8.0 Any tip ? :-D The function is: CREATE OR REPLACE FUNCTION reorder() RETURNS int4 AS $BODY$ DECLARE new_code INTEGER; recs RECORD; validation varchar; vstrname varchar; vstrcgc_cic varchar; vstrquery varchar; vstrupdatevarchar; BEGIN new_code := 1; FOR recs IN (SELECT * FROM table1 ORDER BY is_customer, name) LOOP -- Validation vstrname := replace(recs.name,$$'$$,$$\'$$); vstrupdate := $$UPDATE table1 SET code = $$ || new_code || $$ WHERE old_code = $$ || recs.old_code || $$ AND is_customer $$; IF records.is_customer IS FALSE THEN vstrupdate := vstrupdate || $$ IS true $$; ELSE vstrupdate := vstrupdate || $$ IS false $$; ENDIF; EXECUTE vstrupdate; new_code := new_code + 1 ; END LOOP; RETURN 1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; My table1 is: CREATE TABLE table1 ( code int4, name varchar(50), old_code int4, is_customer bool DEFAULT false ) WITHOUT OIDS; INSERT INTO table1 (code, name, old_code, is_customer) VALUES (9, 'John', 23, true); INSERT INTO table1 (code, name, old_code, is_customer) VALUES (19, 'Rodrigo', 334, true); INSERT INTO table1 (code, name, old_code, is_customer) VALUES (82, 'Fulano', 3484, true); INSERT INTO table1 (code, name, old_code, is_customer) VALUES (1, 'Beltrano', 3454, false); INSERT INTO table1 (code, name, old_code, is_customer) VALUES (4, 'Madicon', 23, false); Cheers, Rodrigo Carvalhaes -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(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] SYNTAX ERROR ON FOR... LOOP
On Wed, Apr 27, 2005 at 02:39:53PM -0300, Rodrigo Carvalhaes wrote: > > I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I > am getting syntax error on this contol structure... [snip] > IF records.is_customer IS FALSE THEN > vstrupdate := vstrupdate || $$ IS true $$; > ELSE > vstrupdate := vstrupdate || $$ IS false $$; > ENDIF; The above should be "END IF" (with a space). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] SYNTAX ERROR ON FOR... LOOP
Hi Rodrigo, - Original Message - From: "Rodrigo Carvalhaes" <[EMAIL PROTECTED]> To: Sent: Wednesday, April 27, 2005 12:39 PM Subject: [SQL] SYNTAX ERROR ON FOR... LOOP Hi Guys, I am having a "simple syntax problem" but very strange... I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I am getting syntax error on this contol structure... If I comment the IF / ELSE / ENDIF the fuction works ... snip vstrupdate := $$UPDATE table1 SET code = $$ || new_code || $$ WHERE old_code = $$ || recs.old_code || $$ AND is_customer $$; IF records.is_customer IS FALSE THEN vstrupdate := vstrupdate || $$ IS true ;$$; I believe you need an ";" after true and false to complete the string as an SQL statement ELSE vstrupdate := vstrupdate || $$ IS false ;$$; ENDIF; EXECUTE vstrupdate; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SYNTAX ERROR ON FOR... LOOP
Rodrigo Carvalhaes <[EMAIL PROTECTED]> writes: > I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I > am getting syntax error on this contol structure... > If I comment the IF / ELSE / ENDIF the fuction works ... plpgsql wants "END IF" not "ENDIF". regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SYNTAX ERROR ON FOR... LOOP
Guys, sometimes I make stupid things... I t was the END IF as all of you said... Thanks for your exists. Cheers, Rodrigo Tom Lane wrote: Rodrigo Carvalhaes <[EMAIL PROTECTED]> writes: I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I am getting syntax error on this contol structure... If I comment the IF / ELSE / ENDIF the fuction works ... plpgsql wants "END IF" not "ENDIF". regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Abraço, Rodrigo Carvalhaes DBA PostgreSQL Moderador grupo siga-br -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo.
