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