how to pass multiple variables to same parameter when calling postgresql function

Question

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

Belayer · Accepted Answer · 2022-03-01 07:17:24Z

2

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, ',')); 
$$;

answered Mar 1, 2022 at 7:17

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2022-03-01 07:19:48Z

1

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']);

answered Mar 1, 2022 at 7:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

how to pass multiple variables to same parameter when calling postgresql function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related