Parse error a in short stored procedure : What's wrong ?

remove a ) at the end, 3 is enough ;-)

Bruno BAGUETTE wrote:

How to build some of those contrib? Can you add one of contrib on the
existing Postgresql without reconfigure, rebuild, and reinstall?

Thanks.
johnl

On Fri, Apr 16, 2004 at 16:49:43 -0500,
John Liu <johnl@emrx.com> wrote:

How to build some of those contrib? Can you add one of contrib on the
existing Postgresql without reconfigure, rebuild, and reinstall?

As long as you haven't done a make clean (or distclean) since your
last build:
cd contrib/whatever
make
make install

I always stop the postmaster when I do this, though I am not sure if you
really need to do that as the contrib stuff just add function libraries
that should get loaded when the are referenced and/or provide scripts
that you run to create the functions or do other things.

Bruno Wolff III <bruno@wolff.to> writes:

John Liu <johnl@emrx.com> wrote:

How to build some of those contrib? Can you add one of contrib on the
existing Postgresql without reconfigure, rebuild, and reinstall?

As long as you haven't done a make clean (or distclean) since your
last build:
cd contrib/whatever
make
make install

Also, if you want to build all the contrib modules, just do the makes
in the contrib directory.

One other point is that most of the more-recently-written contrib
modules support "make installcheck" (though not "make check").

I always stop the postmaster when I do this, though I am not sure if you
really need to do that

You don't.

regards, tom lane

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

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