How to use a variable in a select query postgreSQL

Question

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

Maybe you need TEMPORARY VIEW? It will be dropped at the end of session and will not conflict with other sessions. Details here. — Ihor Romanchenko
– Ihor Romanchenko, Commented Dec 7, 2012 at 13:24

András Váczi · Accepted Answer · 2012-12-07 13:14:10Z

1

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

answered Dec 7, 2012 at 13:14

András Váczi

3,02225 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alroc Over a year ago

Thank you for the answer it works !, yes i want the view to show only Battery_serials = 1

Collectives™ on Stack Overflow

How to use a variable in a select query postgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related