CODEWITHSUNDEEP
sqlpostgresql
Benny Benson
Benny Benson

Reputation: 33

Create Stored Procedure in SQL

i have a school task and i'm wondering why it's not working. My task: Write a statement for a stored procedure, which will give back all the projects the Mitarbeiter took part and the hours he/she spent on each project. (We should use CREATE FUNCTION)

My Solution:

CREATE TYPE ty_projects_hours AS (Projekte VARCHAR, Stunden INTEGER);

CREATE FUNCTION projects_hours (Mitarbeiter_Mail VARCHAR) 
RETURN SETOF ty_projects_hours AS
'SELECT P_ID, Stunden FROM Projektteilnehmer;';

SELECT * FROM projects_hours ([email protected]);

-> So if i

SELECT * FROM projects_hours

i type the mail in the brackets to find the Mitarbeiter.

pgAdmin says: No language given.

Upvotes: 0

Views: 52

Answers (1)

user330315
user330315

Reputation:

"No language given"

What the error message says: you are missing a language sql. You also don't need to create a type for this:

CREATE FUNCTION projects_hours (Mitarbeiter_Mail VARCHAR) 
  RETURN table (projekte varchar, stunden integer)
AS
'SELECT P_ID, Stunden FROM Projektteilnehmer;'
language sql; --<<< this was missing

But you are not using the parameter, so I think you meant:

CREATE FUNCTION projects_hours (mitarbeiter_mail VARCHAR) 
  RETURN table (projekte varchar, stunden integer)
AS
$$
  SELECT P_ID, Stunden 
  FROM Projektteilnehmer
  WHERE teilnehmer = mitarbeiter_mail;
$$ 
language sql;

Then call it with a proper string parameter which needs single quotes:

SELECT * 
FROM projects_hours('[email protected]');

Upvotes: 2

Related Questions