Thread: Function won't compile

Hi,

Does anybody know why the function insert_case() won't compile. When I
INSERT a new record I always get the following error message :

NOTICE:  plpgsql: ERROR during compile of insert_case near line 1
"RROR:  parse error at or near "

Below is the function and trigger. ('Now' is surrounded with single quotes)

CREATE FUNCTION insert_case () RETURNS INTEGER AS '
  BEGIN
    NEW.date_created := ''now'';
  END;
' LANGUAGE 'SQL';

CREATE TRIGGER trg_insert_case BEFORE INSERT ON pfdcase FOR EACH ROW EXECUTE
PROCEDURE insert_case();


Thanks

On Wed, 24 Oct 2001, Joop van Bussel wrote:

> Does anybody know why the function insert_case() won't compile. When I
> INSERT a new record I always get the following error message :
>
> NOTICE:  plpgsql: ERROR during compile of insert_case near line 1
> "RROR:  parse error at or near "
>
> Below is the function and trigger. ('Now' is surrounded with single quotes)
>
> CREATE FUNCTION insert_case () RETURNS INTEGER AS '
>   BEGIN
>     NEW.date_created := ''now'';
>   END;
> ' LANGUAGE 'SQL';
>
> CREATE TRIGGER trg_insert_case BEFORE INSERT ON pfdcase FOR EACH ROW EXECUTE
> PROCEDURE insert_case();

Is it possible that you've got CRLFs at the end of lines instead of just
plain LFs in the function?

> Does anybody know why the function insert_case() won't compile. When I
> INSERT a new record I always get the following error message :
>
> NOTICE:  plpgsql: ERROR during compile of insert_case near line 1
> "RROR:  parse error at or near "
>
> Below is the function and trigger. ('Now' is surrounded with single quotes)
>
> CREATE FUNCTION insert_case () RETURNS INTEGER AS '
>   BEGIN
>     NEW.date_created := ''now'';
>   END;
> ' LANGUAGE 'SQL';

Very simple!  You don't use BEGIN and END within a LANGUAGE 'sql'
function.  You are mixing up sql with plpgsql.

"Joop van Bussel" <jvbussel@natlab.research.philips.com> writes:

> Hi,
>
> Does anybody know why the function insert_case() won't compile. When I
> INSERT a new record I always get the following error message :
>
> NOTICE:  plpgsql: ERROR during compile of insert_case near line 1
> "RROR:  parse error at or near "

Let me guess: you wrote it on DOS and copied it to Unix.  Strip out
the ^M's and you should be OK.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

1. Trigger procedures need to return opaque.
2. 'now' should probably be now().
3. Can the SQL language handle this?

Here is how I did more or less the same:

DROP TRIGGER auto_modified_trig ON en_dirs;
DROP FUNCTION auto_modified();

CREATE FUNCTION auto_modified() RETURNS opaque AS '
 BEGIN
   NEW.modified = now();
   RETURN NEW;
 END;
 ' LANGUAGE 'plpgsql';

CREATE TRIGGER auto_modified_trig BEFORE INSERT OR UPDATE ON en_dirs
FOR EACH ROW EXECUTE PROCEDURE auto_modified();

-- It may not be the _right_ way to do it, but I think it works.

Regards,

Aasmund
On Wed, 24 Oct 2001 15:33:02 +0200, "Joop van Bussel" <jvbussel@natlab.research.philips.com> wrote:
> Hi,
>
> Does anybody know why the function insert_case() won't compile. When I
> INSERT a new record I always get the following error message :
>
> NOTICE:  plpgsql: ERROR during compile of insert_case near line 1
> "RROR:  parse error at or near "
>
> Below is the function and trigger. ('Now' is surrounded with single quotes)
>
> CREATE FUNCTION insert_case () RETURNS INTEGER AS '
>   BEGIN
>     NEW.date_created := ''now'';
>   END;
> ' LANGUAGE 'SQL';
>
> CREATE TRIGGER trg_insert_case BEFORE INSERT ON pfdcase FOR EACH ROW EXECUTE
> PROCEDURE insert_case();
>
>
> Thanks
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

> On Wed, 24 Oct 2001, Joop van Bussel wrote:
>> CREATE FUNCTION insert_case () RETURNS INTEGER AS '
>> BEGIN
>> NEW.date_created := ''now'';
>> END;
>> ' LANGUAGE 'SQL';
     ^^^^^^^^^^^^^^

Looks like plpgsql, not sql, to me.  It's got some other problems too:
no RETURN statement, and it should be declared to return opaque not
integer (though I'm unsure if the system will enforce that).

            regards, tom lane