0

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

user2371684
  • 1,475
  • 5
  • 20
  • 45
  • 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 Mar 28 '22 at 22:29
  • See [here](https://stackoverflow.com/search?q=[postgresql]+query+has+no+destination+for+result+data) –  Mar 29 '22 at 05:21

1 Answers1

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;
jian
  • 4,119
  • 1
  • 17
  • 32
  • Thanks :) what is the difference between a proc in postgres and a user function, and why would the user function be easier? What I would like the proc or the user function to do is when I execute it, I want to supply the char M or F to list either all male or female actors. – user2371684 Apr 02 '22 at 08:07
  • 1
    @user2371684 Generally function, you can easily Input, output. If no output, only input, use store procedure would be easier. – jian Apr 02 '22 at 08:34
  • in my case, it should return a result set of the query with gender as input argument. – user2371684 Apr 02 '22 at 13:09
  • 1
    @user2371684 updated just imitate the manual example. – jian Apr 04 '22 at 03:33
  • Thanks @Mark I need to understand some of the syntax though, so "RETURNS SETOF actors AS $$" what do the $$ mean here. Then the select statement, where gender = $1 mean? And when calling the function AS t1, what's t1 an alias for? Finally when setting the datatype for get actors(text) what if that argument is of a single char, is there a different datatype for it? The datatype text in my example anyways. – user2371684 Apr 04 '22 at 10:44
  • 1
    https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE >>section 38.5.9. SQL Functions Returning Sets . $1 check https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS 38.5.1. Arguments for SQL Functions – jian Apr 04 '22 at 11:47
  • 1
    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 https://wiki.postgresql.org/wiki/Don%27t_Do_This @user2371684 – jian Apr 04 '22 at 11:51
  • 1
    https://stackoverflow.com/questions/54667540/meaning-of-parameter-1-in-sql-function#:~:text=%241%20references%20the%20first%20parameter,SQL%20functions%20in%20Postgres%209.2 @user2371684 – jian Apr 04 '22 at 11:51
  • 1
    what's t1 an alias for? explanation: I just follow the manual example. You can omit alias part. – jian Apr 04 '22 at 11:52