BUG #7784: trouble with pl ERROR: missing FROM-clause entry for table
The following bug has been logged on the website:
Bug reference: 7784
Logged by: Santiago Matiz Vasquez
Email address: smatiz@hotmail.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;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
smatiz wrote
The following bug has been logged on the website:
Bug reference: 7784
Logged by: Santiago Matiz Vasquez
Email address:
smatiz@
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 $$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
From what I can infer from my reading the function you shown here should
work.
As the example is not self-contained it is difficult to prove whether it is
indeed correct or not. The error indicates that the statement "SELECT
valores.iva" was attempted somewhere but that exact construct is not present
in the function shown. I am also not sure exactly where line "31" is in the
supplied code.
I suggest two steps to move along further:
First, make sure that you are executing the function that you think you are.
Search path issues may have cropped up somewhere and a previous, invalid,
version may still be sitting around somewhere.
Next (if necessary), try to narrow down, keep, and point out only the
offending code and whatever is minimally necessary to make it execute.
Unless the example provided is self-contained simply putting out the entire
code without any commentary makes it more difficult for people to provide
help.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-7784-trouble-with-pl-ERROR-missing-FROM-clause-entry-for-table-tp5738676p5738698.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
smatiz@hotmail.com writes:
RAISE NOTICE 'valores.iva (%)', valores.iva;
I see a UTF8 byte order mark (BOM) in front of "valores.iva" there,
which plpgsql is interpreting as part of the record name, so it doesn't
find any matching variable. I'd be the first to agree that the error
message isn't too helpful. But your text editor and your mailer didn't
do you any favors either, if they hid that BOM from you.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs