Обсуждение: create function error

We have gotten these errors every time we try to create a function
through psql.  However, if we run the same statements using phpPgAdmin
or pgAdmin III query tool it works fine.
Here's the error-
ERROR:  unterminated dollar-quoted string at or near "$$
    BEGIN
 NEW.mod_date := now();" at character 63
ERROR:  syntax error at or near "RETURN" at character 9
WARNING:  there is no transaction in progress
ERROR:  unterminated dollar-quoted string at or near "$$
    LANGUAGE plpgsql;" at character 1
ERROR:  function public.setproposalmoddate() does not exist

Here is a script that produces the error-
CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS
$mod_date$
    BEGIN
    NEW.mod_date := now();
        RETURN NEW;
   END;
$mod_date$ LANGUAGE plpgsql;

CREATE TRIGGER dcproposalmodified BEFORE UPDATE
    ON dcproposal  FOR  EACH  ROW
    EXECUTE PROCEDURE setproposalmoddate();

What's more, if we use pgAdmin III to create the function, if we do an
export and then try to import into another db with psql, we get the same
error.
select version() returns PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)

Thanks,

--
Tony Crisera

On Jul 26, 2007, at 13:22 , Tony Crisera wrote:

> ERROR:  unterminated dollar-quoted string at or near "$$
>    BEGIN
> NEW.mod_date := now();" at character 63
> ERROR:  syntax error at or near "RETURN" at character 9
> WARNING:  there is no transaction in progress
> ERROR:  unterminated dollar-quoted string at or near "$$
>    LANGUAGE plpgsql;" at character 1

Note that this is *not* the script you provided below, as $$ does not
appear in the script you provided. Please provide the full output of
the psql session that shows the error (i.e., statements and error
output).

> ERROR:  function public.setproposalmoddate() does not exist

This is irrelevant. It's just telling you the trigger can't be
created because the function doesn't exist.

> Here is a script that produces the error-
> CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS
> $mod_date$
>    BEGIN
>    NEW.mod_date := now();
>        RETURN NEW;
>   END;
> $mod_date$ LANGUAGE plpgsql;

Works fine for me:

test=# CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS
TRIGGER AS $mod_date$
test$#    BEGIN
test$#    NEW.mod_date := now();
test$#        RETURN NEW;
test$#   END;
test$# $mod_date$ LANGUAGE plpgsql;
CREATE FUNCTION
test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5367)
(1 row)

Michael Glaesemann
grzm seespotcode net

Sorry, received information I wasn't aware of.  My understanding was
this was all running on the db server, but the script was actually being
executed through another server (web) that only had a 7.4 client.  This
appears to have been the problem.  Thanks.

Tony Crisera





Michael Glaesemann wrote:
>
> On Jul 26, 2007, at 13:22 , Tony Crisera wrote:
>
>> ERROR:  unterminated dollar-quoted string at or near "$$
>>    BEGIN
>> NEW.mod_date := now();" at character 63
>> ERROR:  syntax error at or near "RETURN" at character 9
>> WARNING:  there is no transaction in progress
>> ERROR:  unterminated dollar-quoted string at or near "$$
>>    LANGUAGE plpgsql;" at character 1
>
> Note that this is *not* the script you provided below, as $$ does not
> appear in the script you provided. Please provide the full output of
> the psql session that shows the error (i.e., statements and error
> output).
>
>> ERROR:  function public.setproposalmoddate() does not exist
>
> This is irrelevant. It's just telling you the trigger can't be created
> because the function doesn't exist.
>
>> Here is a script that produces the error-
>> CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER AS
>> $mod_date$
>>    BEGIN
>>    NEW.mod_date := now();
>>        RETURN NEW;
>>   END;
>> $mod_date$ LANGUAGE plpgsql;
>
> Works fine for me:
>
> test=# CREATE OR REPLACE FUNCTION setproposalmoddate() RETURNS TRIGGER
> AS $mod_date$
> test$#    BEGIN
> test$#    NEW.mod_date := now();
> test$#        RETURN NEW;
> test$#   END;
> test$# $mod_date$ LANGUAGE plpgsql;
> CREATE FUNCTION
> test=# select version();
>
> version
>
----------------------------------------------------------------------------------------------------------------------------------------------

>
> PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
> powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
> build 5367)
> (1 row)
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>
>