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

[Bruno BAGUETTE]

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 - pg******@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

[Pascal Polleunus]

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 - pg******@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Bruno Wolff III]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Bruno Wolff III <br***@wolff.to> writes:

John Liu <jo***@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly