Thread: 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 =3D pnrocuenta;
IF (mesa.nrocuenta IS NULL) THEN
  nromesa :=3D 0;
ELSE
  nromesa :=3D mesa.nrocuenta;
END IF;

/* consulta el valor del producto */
 SELECT INTO valores * FROM inventarios.productos  WHERE
idproducto=3Dpidproducto;

RAISE NOTICE 'valores.iva (%)', =EF=BB=BFvalores.iva;

 valorTOTAL :=3D valores.valor * valores.iva * pcant;
 =



salida :=3D 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 "=EF=BB=BFvalores"
LINE 1: SELECT =EF=BB=BFvalores.iva
               ^
QUERY:  SELECT =EF=BB=BFvalores.iva
CONTEXT:  PL/pgSQL function
financiero.marchar(integer,integer,integer,character varying) line 31 at
RAISE


********** Error **********

ERROR: missing FROM-clause entry for table "=EF=BB=BFvalores"
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=3DFALSE
);
ALTER TABLE inventarios.productos
  OWNER TO dba;

smatiz wrote
> The following bug has been logged on the website:
>=20
> Bug reference:      7784
> Logged by:          Santiago Matiz Vasquez
> Email address:     =20

> smatiz@

> PostgreSQL version: 9.2.2
> Operating system:   MAC LION 10.7.4
> Description:       =20
>=20
>=20
> CREATE OR REPLACE FUNCTION financiero.marchar(pidproducto int,pcant
> int,pnrocuenta int,pcedula character varying(20)) RETURNS character
> varying(100) AS $$
>=20
> result:
> ERROR:  missing FROM-clause entry for table "=EF=BB=BFvalores"
> LINE 1: SELECT =EF=BB=BFvalores.iva
>                ^
> QUERY:  SELECT =EF=BB=BFvalores.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 presen=
t
in the function shown.  I am also not sure exactly where line "31" is in th=
e
supplied code.

I suggest two steps to move along further:

First, make sure that you are executing the function that you think you are=
.=20
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-7=
784-trouble-with-pl-ERROR-missing-FROM-clause-entry-for-table-tp5738676p573=
8698.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

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