Argument not taking the value from Postgres function-CodePudding

I have a simple Postgres function where I want to take table_name as a parameter and pass it into an argument and delete the data from table by condition.

CREATE OR REPLACE FUNCTION cdc.audit_refresh(tablename text)
RETURNS integer AS
$$
BEGIN
   delete from tablename where id<4;
   RETURN(select 1);
END;
$$ LANGUAGE plpgsql;

select cdc.audit_refresh('cdc.adf_test');

But it throws out an error that tablename ERROR: relation "tablename" does not exist in the delete statement.(refer snapshot)

CodePudding user response：

What you want to achieve is to execute Dynamic SQL statements. You can do this with EXECUTE. See more here

CREATE OR REPLACE FUNCTION audit_refresh(tablename text)
    RETURNS integer AS
$$
DECLARE
    stmt TEXT;
BEGIN
    stmt = 'delete from '||tablename||' where id<4;';
    EXECUTE stmt;
    RETURN (select 1);
END
$$ LANGUAGE plpgsql;