org_db=> INSERT INTO organizations (pk_fk_cnt_id,org_name) VALUES (94093,'My Dot Org Organization'); WARNING: Error occurred while executing PL/pgSQL function update_mview_contacts WARNING: line 8 at execute statement ERROR: NEW used in non-rule query
Here's the full code of my stored procedure : ---------------------------------------------
CREATE OR REPLACE FUNCTION update_mview_contacts() RETURNS "trigger" AS ' BEGIN IF TG_OP = ''INSERT'' THEN -- Add the new contact into the materialized view
IF TG_RELNAME = ''people'' THEN EXECUTE ''INSERT INTO mview_contacts (pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial) VALUES('' || quote_literal(NEW.pk_fk_cnt_id) || '', '' || quote_literal(COALESCE(NEW.l_name,'''') || '' '' || COALESCE(NEW.f_name,'''')) || '','' || quote_literal(''people'') || '',LOWER(SUBSTR((COALESCE(NEW.l_name,''''), 1, 1))))''; ELSIF TG_RELNAME = ''organizations'' THEN EXECUTE ''INSERT INTO mview_contacts (pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial) VALUES('' || quote_literal(NEW.pk_fk_cnt_id) || '', '' || quote_literal(NEW.org_name) || '','' || quote_literal(''organization'') || '',LOWER(SUBSTR(NEW.org_name, 1, 1)))'';
replace '',LOWER(SUBSTR((COALESCE(NEW.l_name,''''), 1, 1))))'';
with '','' || quote_literal(LOWER(SUBSTR(COALESCE(NEW.l_name,''), 1, 1)));
otherwise the query that will be executed will contain "NEW.l_name" instead of its value.
PS: the () didn't match again :-p
---------------------------(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
