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

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