pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpesky plpgsql
11+ messages / 7 participants
[nested] [flat]
* pesky plpgsql
@ 2003-04-02 04:31 Walter McGinnis <walter@mars-hq.com>
0 siblings, 2 replies; 11+ messages in thread
From: Walter McGinnis @ 2003-04-02 04:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* pesky plpgsql
@ 2003-04-02 04:37 Walter McGinnis <pg@mars-hq.com>
0 siblings, 1 reply; 11+ messages in thread
From: Walter McGinnis @ 2003-04-02 04:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-02 06:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Walter McGinnis <pg@mars-hq.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2003-04-02 06:03 UTC (permalink / raw)
To: Walter McGinnis <pg@mars-hq.com>; +Cc: pgsql-sql
Walter McGinnis <pg@mars-hq.com> writes:
> 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...
Hmm, it looks fine to me too. What PG version is this? Could you
provide the context (table creation commands, etc) so someone else
could try to run the function?
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-02 17:35 Walter McGinnis <pg@mars-hq.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Walter McGinnis @ 2003-04-02 17:35 UTC (permalink / raw)
To: pgsql-sql
Thanks for your help. I'm going to use this bit of functionality once
or twice, so I went ahead and worked around it in an inelegant, but
effective manner.
Oh well.
Walter
On Tuesday, April 1, 2003, at 10:03 PM, Tom Lane wrote:
> Walter McGinnis <pg@mars-hq.com> writes:
>> 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...
>
> Hmm, it looks fine to me too. What PG version is this? Could you
> provide the context (table creation commands, etc) so someone else
> could try to run the function?
>
> regards, tom lane
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-03 14:47 Walter McGinnis <web@mars-hq.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Walter McGinnis @ 2003-04-03 14:47 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-03 18:01 Josh Berkus <josh@agliodbs.com>
parent: Walter McGinnis <walter@mars-hq.com>
1 sibling, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2003-04-03 18:01 UTC (permalink / raw)
To: Walter McGinnis <walter@mars-hq.com>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-03 18:54 Jan Wieck <JanWieck@Yahoo.com>
parent: Walter McGinnis <walter@mars-hq.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jan Wieck @ 2003-04-03 18:54 UTC (permalink / raw)
To: Walter McGinnis <walter@mars-hq.com>; +Cc: pgsql-sql
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 #
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-03 20:00 Josh Berkus <josh@agliodbs.com>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2003-04-03 20:00 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-03 20:10 Roberto Mello <rmello@cc.usu.edu>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 11+ messages in thread
From: Roberto Mello @ 2003-04-03 20:10 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.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!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-04 01:10 Walter McGinnis <pg@mars-hq.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Walter McGinnis @ 2003-04-04 01:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: pesky plpgsql
@ 2003-04-04 02:09 Roberto Mello <rmello@cc.usu.edu>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Roberto Mello @ 2003-04-04 02:09 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-04-04 02:09 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-02 04:31 pesky plpgsql Walter McGinnis <walter@mars-hq.com>
2003-04-03 18:01 ` Josh Berkus <josh@agliodbs.com>
2003-04-03 14:47 ` Walter McGinnis <web@mars-hq.com>
2003-04-03 20:10 ` Roberto Mello <rmello@cc.usu.edu>
2003-04-03 20:00 ` Josh Berkus <josh@agliodbs.com>
2003-04-04 01:10 ` Walter McGinnis <pg@mars-hq.com>
2003-04-04 02:09 ` Roberto Mello <rmello@cc.usu.edu>
2003-04-03 18:54 ` Jan Wieck <JanWieck@Yahoo.com>
2003-04-02 04:37 pesky plpgsql Walter McGinnis <pg@mars-hq.com>
2003-04-02 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-02 17:35 ` Walter McGinnis <pg@mars-hq.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox