Thread: What is made a mistake with SP?

Hi,

I need create SP that returns cursos so that I can work with them.

I have many SQLs used for search records in database, then I will make
SP to return the results.

The example that I'm trying is:

CREATE OR REPLACE FUNCTION fun_compras_calculado() RETURNS SETOF
tipo_compras_calculado AS '
DECLARE
   linha_compras    RECORD;
   linha_calculada  tipo_compras_calculado;
BEGIN
   linha_calculada.acumulado := 0;
   FOR linha_compras IN
     SELECT id, qtd, produto, unitario FROM compras ORDER BY id
   LOOP
     linha_calculada.id := linha_compras.id;
     linha_calculada.produto := linha_compras.produto;
     linha_calculada.qtd := linha_compras.qtd;
     linha_calculada.unitario := linha_compras.unitario;
     linha_calculada.valor := linha_compras.qtd * linha_compras.unitario;
     linha_calculada.acumulado := linha_calculada.acumulado + linha_calculada.valor;
     RETURN NEXT linha_calculada;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql';

On Jan 23, 2006, at 3:27 AM, Marcos wrote:

> I need create SP that returns cursos so that I can work with them.
>
> I have many SQLs used for search records in database, then I will make
> SP to return the results.
>
> The example that I'm trying is:


I did not notice any obvious errors. It would help to know exactly
what problem you are having. In other words, what does

select * from fun_compras_calculado();

return and if it is not an error, why is it wrong?



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Hi John

Thanks for response.

> In other words, what does
> select * from fun_compras_calculado();
> return and if it is not an error, why is it wrong?

My problem is in CREATE the function, see:

marcos@servidor$ psql teste -U teste < teste.sql
ERRO:  tipo "tipo_compras_calculado" não existe

In english I think that it means

ERROR: type "tipo_compras_calculado" not exists.

This is my problem with this SP...

Marcos.

On Jan 23, 2006, at 8:34 AM, Marcos wrote:

> ERROR: type "tipo_compras_calculado" not exists.

You have a set returning function (RETURNS setof
tipo_compras_calculado), so this means you need to declare this type.
So you should have a CREATE TYPE statement somewhere which defines
the fields of tipo_compras_calculado. Something like:

CREATE TYPE tipo_compras_calculado (
    id text,
    produto integer,
...
);


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL