CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
user2371684
user2371684

Reputation: 1555

stored procedure with parameter in pgadmin

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

Upvotes: 0

Views: 5795

Answers (1)

jian
jian

Reputation: 4824

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;

Upvotes: 1

Related Questions