ERROR: NEW used in non-rule query (was Parse error a in short stored procedure : What's wrong ?) - Mailing list pgsql-general

> 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