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

От:

"Bruno BAGUETTE" <pgsql-ml@baguette.net>

Дата:

17 апреля 2004 г. в 14:24:56

> 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

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

От:

"Bruno BAGUETTE" <pgsql-ml@baguette.net>

Дата:

16 апреля 2004 г. в 06:32:28

Hello,

I have a PL/PGSQL stored procedure that makes me mad currently... (The
stored procedure is a procedure that simulates a materialized view)

It complains about a parse error when I call that procedure :
WARNING:  line 8 at execute statement
ERROR:  parser: parse error at or near "organization" at character 144

So, I think that the error is in that piece of code (I've added the line
number in order to show you the bad line)

5 : IF TG_RELNAME = ''people'' THEN
6 : EXECUTE ''INSERT INTO mview_contacts
(pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial) VALUES(NEW.pk_fk_cnt_id, '' ||
quote_literal(COALESCE(NEW.l_name,'''') || '' '' ||
COALESCE(NEW.f_name,'''')) || '','' ||
''''people'',LOWER(SUBSTR((COALESCE(NEW.l_name,''''), 1, 1))))'';
7 : ELSIF TG_RELNAME = ''organizations'' THEN
8 : EXECUTE ''INSERT INTO mview_contacts
(pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial) VALUES(NEW.pk_fk_cnt_id, '' ||
quote_literal(NEW.org_name) || '','' ||
''''organization'',LOWER(SUBSTR(NEW.org_name, 1, 1))))'';

Do you see something to be wrong in theses lignes ?

Thanks very much for your help !


PS : Is there a tool (on Linux, MacOsX or Windows) that help the writing of
theses stored procedures ?

--------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

Re: build contrib

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

17 апреля 2004 г. в 13:29:24

Bruno Wolff III  writes:
> John Liu  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

build contrib

От:

"John Liu" <johnl@emrx.com>

Дата:

16 апреля 2004 г. в 18:49:49

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

Thanks.
johnl

Re: build contrib

От:

Bruno Wolff III <bruno@wolff.to>

Дата:

17 апреля 2004 г. в 12:38:32

On Fri, Apr 16, 2004 at 16:49:43 -0500,
  John Liu  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.

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

От:

Pascal Polleunus <ppo@beeznest.net>

Дата:

16 апреля 2004 г. в 17:22:56

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


Bruno BAGUETTE wrote:
> Hello,
> 
> I have a PL/PGSQL stored procedure that makes me mad currently... (The
> stored procedure is a procedure that simulates a materialized view)
> 
> It complains about a parse error when I call that procedure :
> WARNING:  line 8 at execute statement
> ERROR:  parser: parse error at or near "organization" at character 144
> 
> So, I think that the error is in that piece of code (I've added the line
> number in order to show you the bad line)
> 
> 5 : IF TG_RELNAME = ''people'' THEN
> 6 : EXECUTE ''INSERT INTO mview_contacts
> (pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial) VALUES(NEW.pk_fk_cnt_id, '' ||
> quote_literal(COALESCE(NEW.l_name,'''') || '' '' ||
> COALESCE(NEW.f_name,'''')) || '','' ||
> ''''people'',LOWER(SUBSTR((COALESCE(NEW.l_name,''''), 1, 1))))'';
> 7 : ELSIF TG_RELNAME = ''organizations'' THEN
> 8 : EXECUTE ''INSERT INTO mview_contacts
> (pk_fk_cnt_id,cnt_name,cnt_type,cnt_initial) VALUES(NEW.pk_fk_cnt_id, '' ||
> quote_literal(NEW.org_name) || '','' ||
> ''''organization'',LOWER(SUBSTR(NEW.org_name, 1, 1))))'';
> 
> Do you see something to be wrong in theses lignes ?
> 
> Thanks very much for your help !
> 
> 
> PS : Is there a tool (on Linux, MacOsX or Windows) that help the writing of
> theses stored procedures ?
> 
> --------------------------------------
> Bruno BAGUETTE - pgsql-ml@baguette.net 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

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

От:

Pascal Polleunus <ppo@beeznest.net>

Дата:

17 апреля 2004 г. в 15:14:03


> 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