How to pass a string parameter to plpgsql function?

Question

I' ve done a function to copy from my db to a csv some rows of the table. I need to pass the name of the table as TEXT parameter.

I call my function in this mode from SQL Workbench/J:

select save_audit_deletions(1000, 'deliveries_audits');

But I have this error:

An error occurred when executing the SQL command:

 select save_audit_deletions(1000, 'deliveries_audits')
 ERROR: relation "table_name" does not exist
 Where: PL/pgSQL function save_audit_deletions(numeric,text) line 10 at SQL statement

This is my function:

CREATE OR REPLACE FUNCTION public.save_audit_deletions(days numeric, table_name text)
  RETURNS void
  LANGUAGE plpgsql
AS
$body$
DECLARE
   interval INT;
   statement_copy text;
   statement_count text;
   copied_rows INTEGER; --number of rows copied by COPY
   backup_rows INTEGER; --number of rows that COPY needs to copy into csv
BEGIN

  UPDATE table_name SET backup = 1 WHERE backup = 0 AND creationdate >= now()::DATE - days AND creationdate < now()::DATE;

  statement_copy := 'COPY (SELECT * FROM ' || table_name || ' WHERE backup = 1) TO ''\var\audiobays\logs\audit\' || table_name || '_deletions_(' || date-days|| ').csv'' CSV DELIMITER '','' HEADER;';
  execute statement_copy
  into copied_rows;

  statement_count := 'SELECT COUNT (*) FROM ' || table_name || ' WHERE backup = 1';
  execute statement_count
  into backup_rows;

    IF copied_rows = backup_rows THEN
        DELETE FROM table_name WHERE backup = 1;
    ELSE
        UPDATE table_name SET backup = 0 WHERE backup = 1;
    END IF;
END;
$body$
  VOLATILE
  COST 100;

COMMIT;

How Can I pass the parameters to the function to allow the function work? Thank you.

The problem isn't passing it in, the problem is your construction of the dynamic sql. Stick statement_copy into a RAISE NOTICE to see what your SQL is. — 404
– 404, Commented Aug 28, 2019 at 10:15

S-Man · Accepted Answer · 2019-08-28 10:18:33Z

5

You cannot use the table name as string directly. In your example the query looks for a table with name table_name, not for a table with the value of your parameter.

But you can create a dynamic SQL string and execute it:

EXECUTE format(
    'UPDATE %I SET backup = 1 ...',
    table_name
);

edited Aug 28, 2019 at 10:18

user330315

answered Aug 28, 2019 at 10:16

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How to pass a string parameter to plpgsql function?

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