Обсуждение: 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.