how to call and send parameter to postgres procedure

Question

i have postgres procedure like this

DROP PROCEDURE IF EXISTS updateTransactionsFromBackups(date text);
        CREATE PROCEDURE updateTransactionsFromBackups(date text)
        LANGUAGE plpgsql
        AS $$
        declare
        path text;
        BEGIN
        path := '/var/www/old_backup_' || date || '.sql';
        COPY old_transactions(id,user_id)
            FROM  path NULL as 'NULL' DELIMITER E'\t' CSV HEADER;
        END;
        $$;

and when i try call procedure like

CALL updateTransactionsFromBackups('2023-03-13'::text);

iget this message :

No procedure matches the given name and argument types. You might need to add explicit type casts.

you are creating a procedure named `updatetransactionsfrombackup` but your CALL statement uses `updatetransactionsfrombackupS` — , Mar 13 '23 at 15:58
I wonder why you define the procedure with a parameter of type ```text``` and call it with a value of type ```varchar```. Eventhough, I think PostgreSQL feels sufficiently smart to call the function anyways and apply some internal cast. But you might try to get your types aligned. — Islingre, Mar 13 '23 at 17:08

score 1 · Answer 1 · answered Mar 17 '23 at 09:51

Basically, your function call should just work as given. Demo: fiddle

Did you use double-quotes in either the function definition or the call? Like "updateTransactionsFromBackups". That would explain the mismatch. My standing advice: use legal, lower case identifiers to begin with, then you don't need to quote, and nothing breaks if you do. See:

Are PostgreSQL column names case-sensitive?

But there is more: What you are trying to do does not work. The COPY command does not support variable substitution. You need dynamic SQL. See:

Error when setting n_distinct using a plpgsql variable

CREATE OR REPLACE PROCEDURE update_transactions_from_backups(_date date)
  LANGUAGE plpgsql AS
$proc$
DECLARE
   _path text := '/var/www/old_backup_' || to_char(_date, 'YYYY_MM_DD') || '.sql';
BEGIN
   IF _path IS NULL THEN
      RAISE EXCEPTION 'Input cannot be NULL!';
   END IF;

   EXECUTE format(
      $$COPY old_transactions(id, user_id)
        FROM %L NULL AS 'NULL' DELIMITER E'\t' CSV HEADER$$
    , _path);
END
$proc$;

Call:

CALL update_transactions_from_backups('2023-03-13');

Note the use of dollar-quotes. See:

Insert text with single quotes in PostgreSQL

Pass the date as actual type date. This way you are safe from SQL injection, as only valid dates are accepted. Format the date with to_char() any way you need it. And use format() with the format specifier %L to concatenate the command string with the right amount of single quotes easily.

Note that this form of COPY requires the privileges of a superuser the pg_read_server_files role.

how to call and send parameter to postgres procedure

1 Answers1