Hello,
I'm trying to build a PL/PGSQL function that will be called by a trigger
which will update a table named 'mview_contacts '. That table plays the
role of a materialized view.
The 'plpgsql' language is installed on that database (there are already
several functions inside) but I don't understand why I can't store that
function in the database.
When I cut/paste the code, psql seems to wait for something at the end:
levure(> ' LANGUAGE 'plpgsql';
levure'>
I've counted 178 ' quotes in that function, so I don't think that is a
missing quote.
There are 38 opening ( and 38 closing ).
All the IF are closed by a END IF.
Do you see what I've missed to get that function to be saved inside the
database ?
CREATE OR REPLACE function update_mview_co ntacts() RETURN OPAQUE 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,c nt_name,cnt_typ e,cnt_initial)' ' ||
quote_literal(' VALUES(NEW.pk_f k_cnt_id, (COALESCE(NEW.l _name,\'\') || \'
\' || COALESCE(NEW.f_ name,\'\')),
\'people\',LOWE R(SUBSTR((COALE SCE(NEW.l_name, \'\') || \' \' ||
COALESCE(NEW.f_ name,\'\')), 1, 1)))');
ELSIF TG_RELNAME = ''organizations '' THEN
EXECUTE ''INSERT INTO mview_contacts
(pk_fk_cnt_id,c nt_name,cnt_typ e,cnt_initial)' ' ||
quote_literal(' VALUES(NEW.pk_f k_cnt_id, NEW.org_name,
\'organization\ ',LOWER(SUBSTR( NEW.org_name, 1, 1)))');
ELSE
RAISE EXCEPTION ''The [%] table is
unsupported by this trigger function'', TG_RELNAME;
END IF;
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('cn t_name') || '' = '' ||
quote_literal(' (COALESCE(l_nam e,\'\') || \' \' ||
COALESCE(f_name ,\'\'))') || '',''
|| quote_ident('cn t_type') || '' = '' ||
quote_literal(' people') || '',''
|| quote_ident('cn t_initial') || '' = ''
|| quote_literal(' LOWER(SUBSTR(NE W.org_name, 1, 1))')
|| '' WHERE mview_contacts. pk_fk_cnt_id
= '' || quote_literal(O LD.pk_fk_cnt_id );
ELSIF TG_RELNAME = ''organizations '' THEN
EXECUTE ''UPDATE mview_contacts SET ''
|| quote_ident('cn t_name') || '' = '' ||
quote_literal(' NEW.org_name') || '',''
|| quote_ident('cn t_type') || '' = '' ||
quote_literal(' organization') || '',''
|| quote_ident('cn t_initial') || '' = ''
|| quote_literal(' LOWER(SUBSTR(NE W.org_name, 1, 1))')
|| '' WHERE mview_contacts. pk_fk_cnt_id
= '' || quote_literal(O LD.pk_fk_cnt_id );
ELSE
RAISE EXCEPTION ''The [%] table is
unsupported by this trigger function'', TG_RELNAME;
END IF;
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_i d'';
ELSIF TG_RELNAME = ''organizations '' THEN
EXECUTE ''DELETE mview_contacts WHERE
pk_fk_cnt_id = OLD.pk_fk_cnt_i d'';
ELSE
RAISE EXCEPTION ''The [%] table is
unsupported by this trigger function'', TG_RELNAME;
END IF;
ELSE
-- Unknown trigger operation
-- ==> Raise an exception
RAISE EXCEPTION ''Unknown trigger function
operation [%]'', TG_OP;
END IF;
END;
' LANGUAGE 'plpgsql';
Thanks you very much in advance for your help.
Regards,
---------------------------------------
Bruno BAGUETTE - pg******@baguet te.net
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
