0
I have three tables:
CREATE TABLE Bebidas(
id SERIAL primary key,
nome varchar(40) NOT NULL,
preco_bebidas decimal(5,2) check (preco_bebidas > 0.00),
qntd int);
CREATE TABLE Shots(
id SERIAL primary key,
nome varchar(40) NOT NULL,
preco_shots decimal(5,2) CHECK (preco_shots > 0.00));
CREATE TABLE Vendas(
id SERIAL,
id_bebidas int references Bebidas(id),
id_shots int references Shots(id),
qntd int CHECK (qntd >= 0),
total decimal(5,2),
data date);
I created a function SELL when Adm sells a drink or shot automatically be inserted into the sales table. However, Postgresql gives the following error:
ERROR: Missing FROM-clause entry for table "preco_bebidas"
LINE 1: ... T INTO sales VALUES(default, variable, null, $3, preco_bebi... (error in price...)QUERY: INSERT INTO VALUES sales(default, variable, null, $3, prices * $3, now())
CONTEXT: PL/pgsql Function sell(Character,Character Varying,integer) line 11 at SQL statement
********** Error **********
ERROR: Missing FROM-clause entry for table "preco_bebidas"
SQL state: 42P01
Context: PL/pgsql Function vender(Character,Character Varying,integer) line 11 at SQL statement
The function is as follows:
CREATE OR REPLACE FUNCTION vender(char, varchar(40), int) RETURNS VOID AS $$
DECLARE
variavel INTEGER;
BEGIN
IF $1 = 'B' THEN
SELECT INTO variavel id from Bebidas
where nome like $2;
INSERT INTO vendas VALUES(default, variavel, null, $3, Bebidas.preco_bebidas * $3, now());
END IF;
IF $1 = 'S' THEN
SELECT INTO variavel id from Shots
where nome like $2;
INSERT INTO vendas VALUES(default, null, variavel, $3, Shots.preco_shots * $3, now());
END IF;
END;
$$
LANGUAGE 'plpgsql';
The problem is not in ALIAS (AS), already tried and not solved. Help me, I’ve been trying for hours and I can’t fix it.
in his
insert
, that doesn’t make sense:Bebidas.preco_bebidas * $3
, you cannot put a reference to a table and a field like this, you would needselect
there, or even play the value before in a variable. Thevalues
must be a value, variable, or select– Ricardo Pontual
You did not specify the definition of the tables involved but, it seems to me, the use of the FROM clause of the UPDATE command would replace this your SELECT. See the documentation.
– anonimo
It worked. Thank you!
– Madson Rocha