[SQL] pesky plpgsql

[Walter McGinnis]

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: [SQL] pesky plpgsql

[Roberto Mello]

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!


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] can i make this sql query more efficiant?

[Franco Bruno Borghesi]

if you're allowed to change the resultset structure, you could do:
SELECT
   event,
   level, 
   count(*)
FROM
   baz
GROUP BY
   event,
   level;   

 event | level | count
---+---+---
 x | 1 | 1
 x | 2 | 1
 x | 3 | 1
 y | 2 | 1
 y | 3 | 2
(5 rows)

of course it doesn't show you the rows where the count is zero.
if you need the zeros, do this

SELECT   
   EL.event,
   EL.level, 
   count(baz.*)
FROM
   (
  SELECT DISTINCT
 B1.event, B2.level 
  FROM 
 baz B1 
 CROSS JOIN baz B2
   ) EL
   LEFT JOIN baz ON (baz.event=EL.event AND baz.level=EL.level)  
GROUP BY
   EL.event,
   EL.level;   

 event | level | count
---+---+---
 x | 1 | 1
 x | 2 | 1
 x | 3 | 1
 y | 1 | 0
 y | 2 | 1
 y | 3 | 2
(6 rows)

hope it helps.

On Thursday 03 April 2003 18:02, Robert Treat wrote:
> create table  baz (event text, level int);
>
> insert into baz values ('x',1);
> insert into baz values ('x',2);
> insert into baz values ('x',3);
> insert into baz values ('y',2);
> insert into baz values ('y',3);
> insert into baz values ('y',3);
>
> select * from baz;
>
>  event | level
> ---+---
>  x | 1
>  x | 2
>  x | 3
>  y | 2
>  y | 3
>  y | 3
> (6 rows)
>
>
> I want to know how many ones, twos, and threes there are for each event:
>
> select
>   event,
>   (select count(*) from baz a
>   where level = 1 and a.event=baz.event) as ones,
>   (select count(*) from baz a
>   where level = 2 and a.event=baz.event) as twos,
>   (select count(*) from baz a
>   where level = 3 and a.event=baz.event) as threes
> from
>baz
> group by
>   event;
>
> which gives me:
>
>  event | ones | twos | threes
> ---+--+--+
>  x |1 |1 |  1
>  y |0 |1 |  2
> (2 rows)
>
>
> which is fine, but I am wondering if there is a better way to do this?
> I'd mainly like to reduce the number of subqueries involved. Another
> improvement would be to not have to explicitly query for each level,
> though this isn't as big since I know the range of levels in advance
> (famous last words for a dba :-)
>
> Thanks in advance,
>
> Robert Treat
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly


pgp0.pgp
Description: signature