CODEWITHSUNDEEP
postgresqlvariablesselectprocedure
Alroc
Alroc

Reputation: 117

How to use a variable in a select query postgreSQL

So here is my problem,

I have a procedure with :

    DECLARE
serialvar INTEGER;

BEGIN
serialvar := NEW.battery_serial;

CREATE OR REPLACE VIEW battery_vue
AS
SELECT * FROM cells WHERE battery_serial = serialvar;
RETURN NEW;
END;

but when the trigger of that procedure is activated i have an error saying :

ERROR: the column « serialvar » does not exist

LINE 3: SELECT * FROM cells WHERE battery_serial = serialVar

Upvotes: 3

Views: 204

Answers (1)

András Váczi
András Váczi

Reputation: 3002

You have to pass the value into a dynamic SQL like

BEGIN

EXECUTE $$CREATE OR REPLACE VIEW battery_vue
AS
SELECT * FROM cells WHERE battery_serial = $$ || NEW.battery_serial;

RETURN NEW;

END;

this way you take the new value, and if it is 1 (for example), then battery_vue will show you only the cells where battery_serial = 1. Is this what you want? (The next insert or whatever will recreate the view, possibly with a different battery_serial.)

Upvotes: 1

Related Questions