CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Daniel Sobczak
Daniel Sobczak

Reputation: 73

Execute INSERT INTO in PLPGSQL using variables as values

I'm trying to create function, which adds a record with given variables as values. My code:

CREATE OR REPLACE FUNCTION ADD_FILM(id INTEGER, t VARCHAR, y INTEGER, p REAL) RETURNS VARCHAR AS $$
DECLARE
    query VARCHAR;
BEGIN
    query = 'insert into films (id_film, title, year_production, price) values ('||id||','||t||','||y||','||p||')';
    EXECUTE query;
RETURN 'OK';
EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
    RAISE NOTICE 'Incorrect ID, next available ID set';
    RETURN 0;
END;
$$ LANGUAGE PLPGSQL;
SELECT ADD_FILM(1,'aaa','2020','10');

Following function execution ends with error. What's wrong with syntax?

ERROR: column "aaa" does not exist

Upvotes: 0

Views: 5302

Answers (2)

armagedescu
armagedescu

Reputation: 2160

I'd rather do like this:

CREATE OR REPLACE FUNCTION ADD_FILM(id INTEGER, t VARCHAR, y INTEGER, p REAL) RETURNS 
VARCHAR AS $$
BEGIN
    insert into films (id_film, title, year_production, price) values (id,t,y,p);
RETURN 'OK';
....
....

See this link: https://www.postgresqltutorial.com/postgresql-create-procedure/

And call it:

CALL ADD_FILM(1,'aaa','2020','10');

Upvotes: 1

pifor
pifor

Reputation: 7882

Another possible solution with EXECUTE:

create table films
(
 id_film int,
 title varchar,
 year_production int,
 price real 
);
CREATE TABLE
CREATE OR REPLACE FUNCTION ADD_FILM(id INTEGER, t VARCHAR, y INTEGER, p REAL) RETURNS VARCHAR AS $$
DECLARE
    query VARCHAR;
BEGIN
    query = 'insert into films (id_film, title, year_production, price) values ($1,$2,$3,$4)';
    EXECUTE query USING id, t, y, p; 
RETURN 'OK';
EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
    RAISE NOTICE 'Incorrect ID, next available ID set';
    RETURN 0;
END;
$$ LANGUAGE PLPGSQL;
CREATE FUNCTION
SELECT ADD_FILM(1,'aaa','2020','10');
 add_film 
----------
 OK
(1 row)

select * from films;
 id_film | title | year_production | price 
---------+-------+-----------------+-------
       1 | aaa   |            2020 |    10
(1 row)

Upvotes: 0

Related Questions