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

1) Read the HINT in the error message, so use PERFORM instead of SELECT. 2) You can't return anything from a PROCEDURE so you are at a dead end anyway. 3) Use a FUNCTION and be more specific about what you actually want to return? Add as update to question. — Adrian Klaver
– Adrian Klaver, Commented Mar 28, 2022 at 22:29

jian · Accepted Answer · 2022-04-04 03:32:39Z

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;

edited Apr 4, 2022 at 3:32

answered Mar 29, 2022 at 5:00

jian

5,0231 gold badge29 silver badges47 bronze badges

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

9 Comments

jian Over a year ago

@user2371684 Generally function, you can easily Input, output. If no output, only input, use store procedure would be easier.

jian Over a year ago

@user2371684 updated just imitate the manual example.

jian Over a year ago

stackoverflow.com/questions/12144284/… postgresql.org/docs/current/… >>section 38.5.9. SQL Functions Returning Sets . $1 check postgresql.org/docs/current/… 38.5.1. Arguments for SQL Functions

jian Over a year ago

for the data type conversion, postgresql will try to cast/conversion. when it fail. obviously there are many tricks. when lazy cast fail, then it will fail. please also check wiki.postgresql.org/wiki/Don%27t_Do_This @user2371684

jian Over a year ago

stackoverflow.com/questions/54667540/… @user2371684

|

Collectives™ on Stack Overflow

stored procedure with parameter in pgadmin

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related