Thread: pesky plpgsql

pesky plpgsql

From
Walter McGinnis
Date:
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



Re: pesky plpgsql

From
Josh Berkus
Date:
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



Re: pesky plpgsql

From
Jan Wieck
Date:
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 #



Re: pesky plpgsql

From
Roberto Mello
Date:
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!



Re: pesky plpgsql

From
Josh Berkus
Date:
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



PowerBuilder and identity column

From
"Stefan"
Date:
Does anybody know hac can be implemented sometning like identity column for
the datawindow object (powerbuilder) with postgres ?



Re: PowerBuilder and identity column

From
"Dan Langille"
Date:
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/



Re: pesky plpgsql

From
Walter McGinnis
Date:
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



Re: pesky plpgsql

From
Roberto Mello
Date:
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

From
"CIC mail"
Date:
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
>



Re: pesky plpgsql

From
"Walter McGinnis"
Date:
> 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