Thread: Function Won't Compile

I'm sure I'm doing something stupid, but this is driving me nuts...

This is the first stored procedure I've ever written in my life.

I have a database calle

jags_content

jags_content has a table in jags_content called

update_flag

update_flag has a column of type timestamp called

content

So far, so good....


I have a file with the following contents:

DROP FUNCTION update_flag_func();
CREATE FUNCTION update_flag_func()
RETURNS text
AS 'UPDATE update_flag SET content=current_timestamp;'
LANGUAGE 'plpgsql';

SELECT update_flag_func();

When I run the 'UPDATE update_flag SET content=current_timestamp' in
the SQL window (pgaccess), it works.

When I type
psql -f udt jags_content

I get an error...

DROP
CREATE
psql:udt:7: NOTICE:  plpgsql: ERROR during compile of update_flag_func
near line 1
psql:udt:7: ERROR:  parse error at or near "UPDATE"

What obvious thing am I missing?

I just upgraded to postgres (7.1.??), I'm running Linux, and
everything else seems to be working (I can run JDBC queries, no
problem...)

Help!

Cheers,
Eric

Eric Chacon wrote:
>
> I'm sure I'm doing something stupid, but this is driving me nuts...
>
> This is the first stored procedure I've ever written in my life.

You're close, debugging plpgsql can be tricky:

> DROP FUNCTION update_flag_func();
> CREATE FUNCTION update_flag_func()
> RETURNS text
> AS 'UPDATE update_flag SET content=current_timestamp;'
> LANGUAGE 'plpgsql';
>
> SELECT update_flag_func();
>

> psql:udt:7: NOTICE:  plpgsql: ERROR during compile of update_flag_func
> near line 1
> psql:udt:7: ERROR:  parse error at or near "UPDATE"

You need a couple of things (untested):

... AS '
BEGIN
  UPDATE update_flag SET content=current_timestamp;
  RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

The BEGIN...END wrapper is needed as is a value to RETURN. There is an
example of this function in my PostgreSQL Notes at http://techdocs.postgresql.org/

HTH

- Richard Huxton