Обсуждение: Stored Procedure Newbie

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

You need the whole sequence of DECLARE ... BEGIN ... END in a plpgsql
function.  Yes, errors are non-obvious with this thing.

chaconeric@hotmail.com (Eric Chacon) writes:
> CREATE FUNCTION update_flag_func()
> RETURNS text
> AS 'UPDATE update_flag SET content=current_timestamp;'
> LANGUAGE 'plpgsql';

> What obvious thing am I missing?

plpgsql wants BEGIN and END decoration around the procedure body.
The example would work as-is (I think) if you said LANGUAGE 'sql'.

            regards, tom lane

On 6 Jul 2001, Eric Chacon wrote:

> 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?

The way you do it, it's better you define LANGUAGE 'sql'
Plpgsql is supposed to have a BEGIN and an END surrounding the statements.

cheers,
thalis

On 6 Jul 2001, Eric Chacon wrote:

> 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?

RTFM? ;)
Seriously, If you're making a plpgsql language function, you're going to
need a begin and end.  Also, if you want it to return text, you need to
make it return something.
The other option is to make it an sql function and add a select after the
update for the return value.