I'm new to PostgreSQL, and trying to learn about store procedure with PostgreSQL. Here are the steps I followed.

1. Installed pgAdmin4
2. Created the Database
3. Created the table "Users" under public schema
4. Created the procedure "GetUserByEmail"

CREATE OR REPLACE PROCEDURE GetUserByEmail  
(      
   Email Varchar(100)
)

LANGUAGE plpgsql AS  
$$  
BEGIN         

 Select * from public."Users" where "Email" = Email
END  
$$;  

When calling it from query tool, it gives an error.

CALL public.GetUserByEmail('[email protected]')

ERROR: procedure public.getuserbyemail(unknown) does not exist LINE 1: CALL public.GetUserByEmail('[email protected]')

^ HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 6

Checked the permission, and the user has execution rights.

Tried different ways but not sure what is wrong.

CodePudding user response：

Are PostgreSQL column names case-sensitive?
if you create table "users"(a int...) then you stick with "users" every time you select/update/delete "users" table.

You can easily imitate 38.5.9. SQL Functions Returning Sets(https://www.postgresql.org/docs/current/xfunc-sql.html)

CREATE FUNCTION getusers(text) RETURNS SETOF "users" AS $$
    SELECT * FROM "users" WHERE email = $1;
$$ LANGUAGE SQL;

SELECT * FROM getusers('hi') AS t1;

stored procedure versus function
demo