i have total of 4 records in my table
id | url | name | description | last_update
---- ------------------------------------ --------------------- ------------- -------------
1 | https://www.postgresqltutorial.com | PostgreSQL Tutorial | |
2 | http://www.oreilly.com | O'Reilly Media | |
3 | https://www.google.com | Google | | 2013-06-01
4 | http://www.postgresql.org | PostgreSQL | |
i have written a function to delete by passing name as a parameter, now i want to pass multiple names but i am facing error.
CREATE OR REPLACE FUNCTION testing(first_name varchar(255))
RETURNS INTEGER AS
$BODY$
DECLARE emp_id INTEGER;
BEGIN
SELECT id into emp_id from links e where name = first_name;
DELETE FROM links WHERE id = emp_id;
return emp_id;
END
$BODY$
LANGUAGE plpgsql;
select * from testing('Google, PostgreSQL');
Error:- no function matches the given name and argument types. you might need to add explicit type casts
CodePudding user response:
Since you have a comma separated list, you can cast your parameter as an array with string_to_array function then apply the any operator. Further there is no reason for pgplsql, this can be written in a single sql statement, then wrapped it into a sql parameter/function. (see demo)
create or replace procedure testing(first_names varchar(255))
language sql
as $$
delete from links
where name = any(string_to_array(first_names, ','));
$$;
CodePudding user response:
If you want to pass several values, you have to define the function to accept several values. There are two ways:
a variadic function:
CREATE FUNCTION testing(VARIADIC first_name text[]) ...
This is called like
SELECT testing('arg1', 'arg2', 'arg3');
a function that accepts an array as parameter:
CREATE FUNCTION testing(first_name text[]) ...
This is called like
SELECT testing(ARRAY['arg1', 'arg2', 'arg3']);