PostgreSQL DECLARE a text variable and use it into INSERT

Question

I'm trying to declare a text variable and insert it into my movie table.

This is what I'm doing:

DECLARE movie_plot TEXT;
movie_plot := '{test}';

INSERT INTO movie(plot) VALUES (movie_plot);

It gives me this error:

ERROR:  syntax error at or near "TEXT"
LINE 1: DECLARE movie_plot TEXT;
                           ^
SQL state: 42601
Character: 20

I already checked some solved questions similar to mine and this sintax seems correct. I saw that I could use the WITH to solve the problem, but i would like to use the DECLARE.

DECLARE (and thus variables) can only be used inside PL/pgSQL code not in SQL — user330315
– user330315, Commented Nov 29, 2021 at 22:17
"I already checked some solved questions similar to mine and this sintax seems correct." -- Not in SQL in Postgres, as @a_horse_with_no_name already commented. Maybe the other questions targeted another DBMS than Postgres. (SQL Server?). Maybe you want to explain the underlying problem, you try to solve with your approach, to get help on that. — sticky bit
– sticky bit, Commented Nov 29, 2021 at 22:20

Nae · Accepted Answer · 2021-11-29 22:29:31Z

2

Perhaps you want to use current_setting:

SET my_vars.movie_plot = 'test';
INSERT INTO movie(plot) VALUES (current_setting('my_vars.movie_plot'));

answered Nov 29, 2021 at 22:29

Nae

15.5k8 gold badges62 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL DECLARE a text variable and use it into INSERT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related