Re: ERROR: NEW used in non-rule query (was Parse error

> 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