Thread: Help with Procedures in pgsql

Help with Procedures in pgsql

From
Nelio Alves Pereira Filho
Date:
I have the folowing procedure in PL/pgsql:
CREATE FUNCTION set_counter () RETURNS INT AS '
DECLARE nivel_rec record;new_count integer;
BEGINUPDATE nivel SET count=-2;
FOR nivel_rec IN SELECT * FROM nivel LOOP    new_count := set_counter_row (nivel_rec);    END LOOP;
RETURN new_count;
END;'
LANGUAGE 'plpgsql';
I create it, but when I do 'select set_counter()', it saysIFX=# select set_counter();ERROR:  Attribute 'nivel_rec' not
foundIsn'tthat the right sintax to declare variables in pgsql? What's wrong
 
with it?Does my procedure contain any other errors that will appear later?

Thanks

-- 
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil


Re: Help with Procedures in pgsql

From
"Robert B. Easter"
Date:
On Monday 27 November 2000 15:01, Nelio Alves Pereira Filho wrote:
> I have the folowing procedure in PL/pgsql:
>
> CREATE FUNCTION set_counter () RETURNS INT AS '
> DECLARE
>     nivel_rec record;
>     new_count integer;

I always used the syntax like:
nivel_rec nivel%ROWTYPE;

Not sure it matters though.


> BEGIN
>     UPDATE nivel SET count=-2;
>
>     FOR nivel_rec IN SELECT * FROM nivel LOOP
>         new_count := set_counter_row (nivel_rec);
>     END LOOP;


I don't know what set_counter_row() is doing so the problem could be in that 
function.



>
>     RETURN new_count;
> END;'
> LANGUAGE 'plpgsql';
>
>  I create it, but when I do 'select set_counter()', it says
>  IFX=# select set_counter();
>  ERROR:  Attribute 'nivel_rec' not found
>
>  Isn't that the right sintax to declare variables in pgsql? What's wrong
> with it?
>  Does my procedure contain any other errors that will appear later?
>
>
>
>  Thanks

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------