Question 113796 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create a stored procedure to Delete Records postgres

I have created a function to delete multiple records.In our table contain id as type uuid.
We get the input is like array of ids.

  CREATE OR REPLACE FUNCTION public.deletetVersion(item_list uuid[])
  RETURNS TABLE(id uuid[]) 
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE PARALLEL UNSAFE
  ROWS 1000
  AS $BODY$
   BEGIN
       RETURN QUERY
         DELETE FROM version WHERE id = ANY(item_list);
  END; 
 $BODY$;

SELECT * from deletetVersion(Array[‘b6ad1912-e4f1-4419-831a-c70df89ffd63′,’877898f0-2f3f-4890-a658-898e35ffee3a’])

But i got an error like:

Anyone please help me

  ERROR:  function deletetversion(text[]) does not exist
Tags:

2

Answers


  1. 0

    it is because the

    Array['b6ad1912-e4f1-4419-831a-c70df89ffd63','877898f0-2f3f-4890-a658-898e35ffee3a']

    is treated as text[]

    try the following

    Array['b6ad1912-e4f1-4419-831a-c70df89ffd63'::uuid,'877898f0-2f3f-4890-a658-898e35ffee3a'::uuid]

    as a parameter to your function

    for example

      CREATE OR REPLACE FUNCTION public.test_uuid(item_list uuid[])
  RETURNS TABLE(id uuid[]) 
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE PARALLEL UNSAFE
  ROWS 1000
  AS $BODY$
   BEGIN
       RETURN QUERY
         SELECT item_list;
  END; 
 $BODY$;
 
 
 
 SELECT * from test_uuid(Array['b6ad1912-e4f1-4419-831a-c70df89ffd63'::uuid])

    In case of deletion

      CREATE OR REPLACE FUNCTION public.test_uuid(item_list uuid[])
  RETURNS VOID
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE PARALLEL UNSAFE
  ROWS 1000
  AS $BODY$
  BEGIN
   RETURN QUERY
     DELETE from tableName WHERE id = ANY(item_list);

  END; 
  $BODY$;
    Login or Signup to reply.
  2. 0

    Your function should return either setof uuid – i.e. a table of uuid-s – or uuid[]. I would prefer the first. You do not need PL/pgSQL, plain SQL is enough. So the function is:

    create or replace function public.deletetVersion(item_list uuid[])
returns setof uuid language 'sql' as
$$
  delete from version where id = any(item_list) returning id;
$$;

    The version returning an array is a bit more complex:

    create or replace function public.deletetVersion(item_list uuid[])
returns uuid[] language 'sql' as
$$
with t(d_id) as
(
  delete from version where id = any(item_list) returning id
)
select array_agg(d_id) from t;
$$;

    And – as @Ibrahimshamma says – you may need to cast the argument to uuid[].

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search