From: | smatiz(at)hotmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #7784: trouble with pl ERROR: missing FROM-clause entry for table |
Date: | 2013-01-03 18:20:10 |
Message-ID: | E1TqpOk-0007By-DS@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 7784
Logged by: Santiago Matiz Vasquez
Email address: smatiz(at)hotmail(dot)com
PostgreSQL version: 9.2.2
Operating system: MAC LION 10.7.4
Description:
CREATE OR REPLACE FUNCTION financiero.marchar(pidproducto int,pcant
int,pnrocuenta int,pcedula character varying(20)) RETURNS character
varying(100) AS $$
DECLARE
valores inventarios.productos%ROWTYPE;
mesa general.mesa%ROWTYPE;
nromesa int;
valorTOTAL real;
valorIVA integer;
salida character varying(100);
BEGIN
/*
Busca dentro de la tabla de numero de cuenta la asociacion con la mesa
si no la encuentra pone 0
*/
SELECT * INTO mesa FROM general.mesa WHERE nrocuenta = pnrocuenta;
IF (mesa.nrocuenta IS NULL) THEN
nromesa := 0;
ELSE
nromesa := mesa.nrocuenta;
END IF;
/* consulta el valor del producto */
SELECT INTO valores * FROM inventarios.productos WHERE
idproducto=pidproducto;
RAISE NOTICE 'valores.iva (%)', valores.iva;
valorTOTAL := valores.valor * valores.iva * pcant;
salida := cast(valores.idproducto as character varying(5))|| '|' ||
valores.nombre || '|' || CAST(valores.valor AS character varying(10)) || '|'
|| CAST(pcant AS character varying(10)) ;
/*crea el cargo*/
INSERT INTO
financiero.cargos(valoru,idmesa,cedula,idproducto,cant,nrocuenta,valor_iva,iva)
VALUES
(valorTOTAL,nromesa,pcedula,pidproducto,pcant,pnrocuenta,0,valores.iva);
RETURN salida;
END;
$$ LANGUAGE plpgsql;
execute :
select financiero.marchar(1,1,1,'1');
result:
ERROR: missing FROM-clause entry for table "valores"
LINE 1: SELECT valores.iva
^
QUERY: SELECT valores.iva
CONTEXT: PL/pgSQL function
financiero.marchar(integer,integer,integer,character varying) line 31 at
RAISE
********** Error **********
ERROR: missing FROM-clause entry for table "valores"
SQL state: 42P01
Context: PL/pgSQL function
financiero.marchar(integer,integer,integer,character varying) line 31 at
RAISE
table :
CREATE TABLE inventarios.productos
(
idproducto integer NOT NULL,
nombre character varying(200),
valor integer,
iva real DEFAULT 1.16,
CONSTRAINT pk_producto PRIMARY KEY (idproducto)
)
WITH (
OIDS=FALSE
);
ALTER TABLE inventarios.productos
OWNER TO dba;
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2013-01-03 18:27:48 | Re: BUG #7783: lower & upper function incorrect work |
Previous Message | alex.shniperson | 2013-01-03 09:07:49 | BUG #7783: lower & upper function incorrect work |