471,078 Members | 831 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,078 software developers and data experts.

Unable to create a PL/PGSL function : Did I miss something ?

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_contacts() 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,cnt_name,cnt_type,cnt_initial)'' ||
quote_literal('VALUES(NEW.pk_fk_cnt_id, (COALESCE(NEW.l_name,\'\') || \'
\' || COALESCE(NEW.f_name,\'\')),
\'people\',LOWER(SUBSTR((COALESCE(NEW.l_name,\'\') || \' \' ||
COALESCE(NEW.f_name,\'\')), 1, 1)))');
ELSIF TG_RELNAME = ''organizations'' THEN
EXECUTE ''INSERT INTO mview_contacts
(pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial)'' ||
quote_literal('VALUES(NEW.pk_fk_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('cnt_name') || '' = '' ||
quote_literal('(COALESCE(l_name,\'\') || \' \' ||
COALESCE(f_name,\'\'))') || '',''
|| quote_ident('cnt_type') || '' = '' ||
quote_literal('people') || '',''
|| 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);
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;
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;
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******@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #1
1 2267
On Sun, Feb 08, 2004 at 01:47:51AM +0100, Bruno BAGUETTE wrote:
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.
You forgot to escape some quotes:
quote_literal('people') || '','' ^ ^ || quote_ident('cnt_initial') || '' = '' ^ ^ || quote_literal('LOWER(SUBSTR(NEW.org_name, 1, 1))') ^ ^
|| 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))')
Several in there

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAJY1XY5Twig3Ge+YRAvW1AJ9GtZQ3MGpcqakdYm47lA TJFxXt5gCfagzO
gLzOe3nZGsufv+rITFeMnec=
=Zgto
-----END PGP SIGNATURE-----

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Miguel Dias Moura | last post: by
6 posts views Thread by Stan Cook | last post: by
15 posts views Thread by harvey | last post: by
19 posts views Thread by loudking | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.