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 — user330315
– user330315, Commented Mar 13, 2023 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
– Islingre, Commented Mar 13, 2023 at 17:08

Erwin Brandstetter · Accepted Answer · 2023-03-17 09:51:46Z

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.

Collectives™ on Stack Overflow

how to call and send parameter to postgres procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related