CODEWITHSUNDEEP
postgresql
Sarfaraz Mak
Sarfaraz Mak

Reputation: 141

Plpgsql: How can i delete a specific row from view by passing the value from function?

I am trying to delete a row from view by passing the value from function but getting an error the column rollno does not exist.

For example:

Table 

create table tbl1  
( rollnumber int,  
name varchar);

Inserting some rows 

insert into tbl1 values(1,'abc');  
insert into tbl1 values(2,'def');  
insert into tbl1 values(3,'ghi');

View 

create view view1 as select * from tbl1;

Deleting a specific row from view1 from my function. 

create or replace function function1(rollno int)  
returns void as  
$body$  
declare  
        temp varchar;  
begin  
        temp:='delete from view1 where rollnumber=rollno';  
        execute temp;  
end;  
$body$  
language plpgsql;

Upvotes: 0

Views: 2605

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324385

This:

    temp:='delete from view1 where rollnumber=rollno';

cannot possibly work. You're not substituting a variable, you're literally running that query as written.

I think you might have meant:

    temp:='delete from view1 where rollnumber = ' || quote_literal(rollno);
    execute temp;

which is better written as a parameterized query:

EXECUTE 'delete from view1 where rollnumber = $1' USING rollno;

but in this case, there's not any need to make it dynamic SQL at all, so you can just write:

create or replace function function1(rollno int)  
returns void as $$
delete from view1 where rollnumber=$1;
$$
language SQL;

Upvotes: 3

Related Questions