stored procedure with parameter in pgadmin

Question

I am trying to create a stored procedure via pgadmin4. I have a actors table with one of the columns being gender with the data type as character either M or F, so what I want to create is stored procedure where I supply the gender as a single char 'M' or 'F'

This is my code:

CREATE or replace PROCEDURE actorGender(sex character)
language plpgsql    
as $$
begin
 select * from actors where gender = sex;
end;$$

call actorGender('M')

But I get the following error:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function actorgender(character) line 3 at SQL statement SQL state: 42601

Answer 1

db fiddle But I don't know how to decompose it. PROCEDURE when you call it, you also need to specify all the IN and OUT. unlike the function. you can just call it with select function(in argument).

begin;
create  table actors(actorid bigint, gender text,actorname text);
insert into actors (actorid, gender, actorname) values (1,'hi', 'etc');
insert into actors (actorid, gender, actorname) values (2,'hello', 'etc1');
commit;

CREATE or replace PROCEDURE actorgender(in sex text, out a  actors)
language plpgsql
as $$
begin
    select * from actors where gender = sex into a;

end
$$;

call it with INPUT and OUTPUT parameter.

  call actorgender('hi',null::actors);

It will return (1,hi,etc)
use function would be must easier.

---

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE

    CREATE FUNCTION getactors(text) RETURNS SETOF actors AS $$
    SELECT * FROM actors WHERE gender = $1;
$$ LANGUAGE SQL;

--call it      
SELECT * FROM getactors('hi') AS t1;