Thread: pesky plpgsql
Guess I'm an April Fool. When I define the following psql returns
"CREATE":
-- update all galleries that are categories with their skey and rkey
values
CREATE FUNCTION update_cat_keys() RETURNS integer AS '
declare v_row RECORD;
begin for v_row in select gallery_id,
imagedb_gallery_hierarchy_skey(gallery_id, NULL, '''') as skey,
imagedb_gallery_hierarchy_rskey(gallery_id, NULL, '''') as rskey from
imagedb_galleries where category_p = ''t'' loop
update imagedb_galleries set skey = v_row.skey, rskey = v_row.rskey where gallery_id = v_row.gallery_id; end
loop;
return 1;
end;
' LANGUAGE 'plpgsql';
When I call the function I get the following error:
devpps=# select update_cat_keys();
NOTICE: plpgsql: ERROR during compile of update_cat_keys near line 2
ERROR: parse error at or near ";"
The select in the loop runs fine in psql. So I'm stumped...
I'm pretty ignorant about plpgsql, I'm used to pl/sql.
TIA,
Walter
Walter, > CREATE FUNCTION update_cat_keys() RETURNS integer AS ' > declare > v_row RECORD; What's in-between v_row and RECORD? Maybe a non-standard tab character? That's the only thing I can see, as the rest of your syntax looks fine. (FYI, in PL/pgSQL, your function is not compiled until you run it.) -- -Josh BerkusAglio Database SolutionsSan Francisco
Walter McGinnis wrote: > > Guess I'm an April Fool. When I define the following psql returns > "CREATE": > [...] > devpps=# select update_cat_keys(); > NOTICE: plpgsql: ERROR during compile of update_cat_keys near line 2 > ERROR: parse error at or near ";" > > The select in the loop runs fine in psql. So I'm stumped... > > I'm pretty ignorant about plpgsql, I'm used to pl/sql. What version of PostgreSQL and did you by any chance use a DOS editor (or anything that creates CRLF end of lines for that matter)? Older versions of PL/pgSQL didn't like that. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Thu, Apr 03, 2003 at 10:01:36AM -0800, Josh Berkus wrote: > Walter, > > > CREATE FUNCTION update_cat_keys() RETURNS integer AS ' > > declare > > v_row RECORD; > > What's in-between v_row and RECORD? Maybe a non-standard tab character? "non-standard tab character"? Are you saying tab is non-standard or that there's some sort of weird tab character that could be being used in this case? I've always used tabs in my PL/pgSQL functions and it works just fine. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + A poor excuse is better than no excuse!
Roberto, > "non-standard tab character"? Are you saying tab is non-standard or that > there's some sort of weird tab character that could be being used in this > case? > > I've always used tabs in my PL/pgSQL functions and it works just fine. Yes, but some older DOS or other editors, or graphical word processors, use a tab which is *not* the standard ASCII character, or an end of line marker which is not CR or CRLF. For example, try creating a PL/pgSQL function using MS WORD 2000 .... -- -Josh BerkusAglio Database SolutionsSan Francisco
Does anybody know hac can be implemented sometning like identity column for the datawindow object (powerbuilder) with postgres ?
On 3 Apr 2003 at 23:48, Stefan wrote: > Does anybody know hac can be implemented sometning like identity > column for the datawindow object (powerbuilder) with postgres ? Please elaborate upon that. I use PowerBuilder. -- Dan Langille : http://www.langille.org/
Well I discovered on my own that it was the tabs that were causing the problem. Which is pretty strange considering I was using emacs on a remote BSD machine. Walter On Thursday, April 3, 2003, at 12:00 PM, Josh Berkus wrote: > Roberto, > >> "non-standard tab character"? Are you saying tab is non-standard or >> that >> there's some sort of weird tab character that could be being used in >> this >> case? >> >> I've always used tabs in my PL/pgSQL functions and it works just fine. > > Yes, but some older DOS or other editors, or graphical word > processors, use a > tab which is *not* the standard ASCII character, or an end of line > marker > which is not CR or CRLF. For example, try creating a PL/pgSQL > function > using MS WORD 2000 .... > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
On Thu, Apr 03, 2003 at 12:00:24PM -0800, Josh Berkus wrote: > > Yes, but some older DOS or other editors, or graphical word processors, use a > tab which is *not* the standard ASCII character, or an end of line marker > which is not CR or CRLF. For example, try creating a PL/pgSQL function > using MS WORD 2000 .... Ahh, I see. Good to know. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Mie spel czecher iz awn the phritz.
UNSCRIBE Atentamente M. en C. César Saúl Guzmán Rentería Jefe de la Unidad de Desarrollo de Aplicaciones Subdirección de Investigación Aplicada Centro de Investigación en Computación Teléfono: 57-29-60-00, extensiones: 56551, 56587 ----- Original Message ----- From: "Walter McGinnis" <walter@mars-hq.com> To: <pgsql-sql@postgresql.org> Sent: Tuesday, April 01, 2003 10:31 PM Subject: [SQL] pesky plpgsql > Guess I'm an April Fool. When I define the following psql returns > "CREATE": > > -- update all galleries that are categories with their skey and rkey > values > CREATE FUNCTION update_cat_keys() RETURNS integer AS ' > declare > v_row RECORD; > begin > for v_row in select gallery_id, > imagedb_gallery_hierarchy_skey(gallery_id, NULL, '''') as skey, > imagedb_gallery_hierarchy_rskey(gallery_id, NULL, '''') as rskey from > imagedb_galleries where category_p = ''t'' loop > > update imagedb_galleries > set skey = v_row.skey, rskey = v_row.rskey > where gallery_id = v_row.gallery_id; > end loop; > return 1; > end; > ' LANGUAGE 'plpgsql'; > > When I call the function I get the following error: > > devpps=# select update_cat_keys(); > NOTICE: plpgsql: ERROR during compile of update_cat_keys near line 2 > ERROR: parse error at or near ";" > > The select in the loop runs fine in psql. So I'm stumped... > > I'm pretty ignorant about plpgsql, I'm used to pl/sql. > > TIA, > > Walter > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
> Walter, > >> CREATE FUNCTION update_cat_keys() RETURNS integer AS ' >> declare >> v_row RECORD; > > What's in-between v_row and RECORD? Maybe a non-standard tab > character? > You hit the nail on the head. I went on to other stuff and figured out that the tabs in were what was causing the problem. Walter