Function won't compile

"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

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.

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:

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?

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