Обсуждение: Parse error a in short stored procedure : What's wrong ?
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
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
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
ERROR: NEW used in non-rule query (was Parse error a in short stored procedure : What's wrong ?)
От
"Bruno BAGUETTE"
Дата:
> 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