> remove a ) at the end, 3 is enough ;-)
You were right, and I found also some misplaced quotes which I replaced by
the quote_literal() function.
I'm now getting another error :
WARNING: Error occurred while executing PL/pgSQL function
update_mview_contacts
WARNING: line 8 at execute statement
ERROR: NEW used in non-rule query
I don't understand why that stored procedure run very well when it's called
by an UPDATE trigger, and it fails when it is called by an INSERT trigger...
:-(
This procedure is called by theses triggers :
CREATE TRIGGER maj_mview_contacts
AFTER INSERT OR DELETE OR UPDATE ON people
FOR EACH ROW
EXECUTE PROCEDURE update_mview_contacts();
CREATE TRIGGER maj_mview_contacts
AFTER INSERT OR DELETE OR UPDATE ON organizations
FOR EACH ROW
EXECUTE PROCEDURE update_mview_contacts();
And my test is that one :
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)))'';
ELSE
RAISE EXCEPTION ''The [%] table is unsupported by this trigger function'',
TG_RELNAME;
END IF;
RETURN NEW;
ELSIF TG_OP = ''UPDATE'' THEN
-- Update the contact infos in the materialized view
IF TG_RELNAME = ''people'' THEN
EXECUTE ''UPDATE mview_contacts SET ''
|| quote_ident(''cnt_name'') || '' = '' ||
quote_literal((COALESCE(NEW.l_name,'''') || '' '' ||
COALESCE(NEW.f_name,''''))) || '',''
|| quote_ident(''cnt_type'') || '' = '' || quote_literal(''people'') ||
'',''
|| quote_ident(''cnt_initial'') || '' = '' ||
quote_literal(LOWER(SUBSTR(NEW.l_name, 1, 1)))
|| '' WHERE mview_contacts.pk_fk_cnt_id = '' ||
quote_literal(OLD.pk_fk_cnt_id);
ELSIF TG_RELNAME = ''organizations'' THEN
EXECUTE ''UPDATE mview_contacts SET ''
|| quote_ident(''cnt_name'') || '' = '' || quote_literal(NEW.org_name) ||
'',''
|| quote_ident(''cnt_type'') || '' = '' || quote_literal(''organization'')
|| '',''
|| quote_ident(''cnt_initial'') || '' = '' ||
quote_literal(LOWER(SUBSTR(NEW.org_name, 1, 1)))
|| '' WHERE mview_contacts.pk_fk_cnt_id = '' ||
quote_literal(OLD.pk_fk_cnt_id);
ELSE
RAISE EXCEPTION ''The [%] table is unsupported by this trigger function'',
TG_RELNAME;
END IF;
RETURN NEW;
ELSIF TG_OP = ''DELETE'' THEN
-- Remove the contact from the materialized view
IF TG_RELNAME = ''people'' THEN
EXECUTE ''DELETE mview_contacts WHERE pk_fk_cnt_id = OLD.pk_fk_cnt_id'';
ELSIF TG_RELNAME = ''organizations'' THEN
EXECUTE ''DELETE mview_contacts WHERE pk_fk_cnt_id = OLD.pk_fk_cnt_id'';
ELSE
RAISE EXCEPTION ''The [%] table is unsupported by this trigger function'',
TG_RELNAME;
END IF;
RETURN OLD;
ELSE
-- Unknown trigger operation
-- ==> Raise an exception
RAISE EXCEPTION ''Unknown trigger function operation [%]'', TG_OP;
END IF;
END;
'
LANGUAGE plpgsql;
Where is my error(s) ? What can I do to avoid that (or theses) error(s) ?
Thanks really much in advance for your help !
--
Bruno Baguette - pgsql-ml@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
