Re: [SQL] dynmic column names inside trigger?
I would do something like this (not tested, but conceptually working): CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text) AS $body$ BEGIN cleantext = translate(webtext, E'\x92\x96', '''-'); cleantext = regexp_replace(cleantext, E'\x9c', 'oe', 'g'); cleantext = regexp_replace(cleantext, E'\x85', '...', 'g'); END; $body$ LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT; CREATE or replace FUNCTION func_trig_tbl_x() RETURNS "trigger" AS $body$ BEGIN if old.story is not null and new.story != old.story then new.story = sanitize_text(new.story); end if; --checks on other field can be included here, eg if old.otherfield is not null and new.otherfield != old.otherfield then new.otherfield = sanitize_text(new.otherfield); end if; return new; END; $body$ LANGUAGE plpgsql; CREATE TRIGGER trig_tbl_x BEFORE INSERT or update ON tbl_x FOR EACH ROW EXECUTE PROCEDURE func_trig_tbl_x(); Now for tbl_y you can create a function func_trig_tbl_y (which again calls sanitize_text for any field you like) and a trigger trig_tbl_x. It might be necessary to replace "cleantext OUT text" by "cleantext IN OUT text". I haven't tested it. >>> Louis-David Mitterrand <[EMAIL PROTECTED]> 2007-11-20 17:12 >>> Hi, I've got this trigger to clean up text entered in web forms: CREATE or replace FUNCTION sanitize_text() RETURNS "trigger" AS $$ declare begin if old.story is not null and new.story != old.story then new.story = translate(new.story, E'\x92\x96', '''-'); new.story = regexp_replace(new.story, E'\x9c', 'oe', 'g'); new.story = regexp_replace(new.story, E'\x85', '...', 'g'); end if; return new; end; $$ LANGUAGE plpgsql; CREATE TRIGGER sanitize_text_trig BEFORE INSERT or update ON story FOR EACH ROW EXECUTE PROCEDURE sanitize_text(); I'd like to use it on other tables an columns but how can the column name be dynamic inside the procedure. Passing the column name in the trigger declaration and using it as NEW.TG_ARGV[0] seems out of the question. Is there another solution out there? Thanks, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] PL argument max size, and doubt
I was doing some tests to see if I could find a max size for an argument of type TEXT in a PL/PgSQL function (BTW, which it that limit if it exists?). So I made the function to test: CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ BEGIN EXECUTE $ins1$ INSERT INTO funcdatogrande VALUES (default,$ins1$ || quote_literal($1) || $ins2$)$ins2$; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $body$ LANGUAGE 'plpgsql'; What bothers me is that the INSERT passes ok (the data is inserted) but the function is returning false on any all to it. I hope not to have a conceptual problem. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL argument max size, and doubt
On Nov 21, 2007 8:23 AM, Martin Marques <[EMAIL PROTECTED]> wrote: > (BTW, which it that limit if it exists?). "In any case, the longest possible character string that can be stored is about 1 GB." See: http://www.postgresql.org/docs/8.2/static/datatype-character.html > So I made the function to test: > > CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ > BEGIN > EXECUTE $ins1$ > INSERT INTO funcdatogrande VALUES (default,$ins1$ || > quote_literal($1) || $ins2$)$ins2$; > IF FOUND THEN > RETURN TRUE; > ELSE > RETURN FALSE; > END IF; > END; > $body$ LANGUAGE 'plpgsql'; > > > What bothers me is that the INSERT passes ok (the data is inserted) but > the function is returning false on any all to it. I hope not to have a > conceptual problem. I don't think EXECUTEing sets FOUND to true. Try: CREATE OR REPLACE FUNCTION DATOGRANDE(TEXT) RETURNS BOOLEAN AS $$ BEGIN INSERT INTO FUNCDATOGRANDE VALUES (DEFAULT,$1); IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$ LANGUAGE 'PLPGSQL'; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PL argument max size, and doubt
Rodrigo De León escribió: On Nov 21, 2007 8:23 AM, Martin Marques <[EMAIL PROTECTED]> wrote: (BTW, which it that limit if it exists?). "In any case, the longest possible character string that can be stored is about 1 GB." See: http://www.postgresql.org/docs/8.2/static/datatype-character.html I was asking about the limit in the argument. Is it the same as the limits the types have in table definition? So I made the function to test: CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ BEGIN EXECUTE $ins1$ INSERT INTO funcdatogrande VALUES (default,$ins1$ || quote_literal($1) || $ins2$)$ins2$; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $body$ LANGUAGE 'plpgsql'; What bothers me is that the INSERT passes ok (the data is inserted) but the function is returning false on any all to it. I hope not to have a conceptual problem. I don't think EXECUTEing sets FOUND to true. Try: CREATE OR REPLACE FUNCTION DATOGRANDE(TEXT) RETURNS BOOLEAN AS $$ BEGIN INSERT INTO FUNCDATOGRANDE VALUES (DEFAULT,$1); IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$ LANGUAGE 'PLPGSQL'; I have always heard that modification queries should be EXECUTED in PL. AFAICR. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL argument max size, and doubt
Martin Marques <[EMAIL PROTECTED]> writes: > Rodrigo De León escribió: >> "In any case, the longest possible character string that can be stored >> is about 1 GB." >> > I was asking about the limit in the argument. Is it the same as the > limits the types have in table definition? Yeah, ultimately this is a palloc() restriction on the size of any one data value. > I have always heard that modification queries should be EXECUTED in PL. > AFAICR. Run far away from whatever source gave you that advice... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] update on join ?
Hi, I'd like to update a table efficiently where the relevant select-info is in another table that is foreign-linked. Stupid example. 2 tables: things (thing_id integer, name varchar(100), color varchar(100)) inventory (item_id integer, thing_fk integer references things (thing_id), number) For some reason I'd want to set the number of every red item to 0. This inventory doesn't contain the color but the foreign key to the other table where the color is found. I tried UPDATE things JOIN inventory ON things.thing_id = inventory.thing_fk SET number = 0 WHERE color = 'red' PSQL didn't like the JOIN though. It works like this: UPDATE inventory SET number = 0 WHERE thing_fk IN (SELECT thing_id FROM things WHERE color = 'red') It's effective but is it efficient, too? Regards ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] update on join ?
Andreas wrote: I'd like to update a table efficiently where the relevant select-info is in another table that is foreign-linked. Stupid example. 2 tables: things (thing_id integer, name varchar(100), color varchar(100)) inventory (item_id integer, thing_fk integer references things (thing_id), number) For some reason I'd want to set the number of every red item to 0. This inventory doesn't contain the color but the foreign key to the other table where the color is found. I tried UPDATE things JOIN inventory ON things.thing_id = inventory.thing_fk SET number = 0 WHERE color = 'red' PSQL didn't like the JOIN though. It works like this: UPDATE inventory SET number = 0 WHERE thing_fk IN (SELECT thing_id FROM things WHERE color = 'red') It's effective but is it efficient, too? What about: UPDATE things, inventory SET number = 0 WHERE color = 'red' AND things.thing_id = inventory.thing_fk ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] update on join ?
--- On Wed, 11/21/07, Andreas <[EMAIL PROTECTED]> wrote:> > UPDATE inventory > SET number = 0 > WHERE thing_fk IN (SELECT thing_id FROM things WHERE color > = 'red') This is a perfectly acceptable ANSI-SQL update statement. Here is non-ANSI update statement that you are probably after: UPDATE Inventory SET number = 0 FROM Things WHERE Inventory.thing_fk = Things.thing_id AND Things.color = 'red'; IIRC, Joe Celko referrers to this syntax as "T-SQL". Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] update on join ?
> I tried > > UPDATE things JOIN inventory ON things.thing_id = inventory.thing_fk > SET number = 0 > WHERE color = 'red' > use the cool "from" clause in the update update things t set number = 0 from inventory i where t.thing_id = i.thing_fk and i.color = 'red'; Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Bad Schema Design or Useful Trick?
Below I've included sample table definitions for a vertically partitioned disjunctive table hierarchy. I wanted to point out the use of the composite primary key declaration that is applied to two columns that are clearly not a candidate key. However, using the badly defined primary key allows for referential integrity to nicely handle the de-normalization between the main table and sub tables that is inherent with this type of data model. Would using a primary key in this manner be a decision that I will regret in the long run? If so, can any explain why? The parent table is parts with the child table pumps and hardware. CREATE TABLE Parts ( part_nbr varchar( 100 ) UNIQUE NOT NULL, part_type varchar( 20 ) NOT NULL, unit_cost numeric(7,2) NOT NULL DEFAULT 0 CHECK( unit_cost >= 0 ), description text NOT NULL, CONSTRAINT parts_primary_key PRIMARY KEY( part_nbr, part_type ), CONSTRAINT only_defined_part_types CHECK( part_type IN ( 'pump', 'bolt', 'nut'))); CREATE TABLE Pumps ( part_nbr varchar( 100 ) PRIMARY KEY, part_typevarchar( 20 ) NOT NULL CHECK( part_type = 'pump' ), volumn real NOT NULL CHECK( volumn > 0 ), motorhp_size varchar( 4 ) NOT NULL REFERENCES Motortypes( motorhp_size), CONSTRAINT parts_foreign_key FOREIGN KEY( part_nbr, part_type ) REFERENCES Parts( part_nbr, part_type) ON DELETE CASCADE ON UPDATE CASCADE); CREATE TABLE Hardware ( part_nbrvarchar( 100 ) PRIMARY KEY, part_type varchar( 20 ) NOT NULL CHECK( part_type IN ( 'bolt', 'nut' ), thread_size varchar( 4 ) NOT NULL REFERENCES Threadtypes( Thread_size ), grading varchar( 4 ) NOT NULL REFERENCES Gradingtypes( grading ), CONSTRAINT parts_foreign_key FOREIGN KEY( part_nbr, part_type ) REFERENCES Parts( part_nbr, part_type) ON DELETE CASCADE ON UPDATE CASCADE); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
