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
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_contentI 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
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_contentI 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.