Error inserting in table: "Missing FROM-clause entry for table..."

Asked

Viewed 1,410 times

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 need select there, or even play the value before in a variable. The values must be a value, variable, or select

  • 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.

  • It worked. Thank you!

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.