CODEWITHSUNDEEP
postgresqlplpgsql
hari kishore
hari kishore

Reputation: 1

I am getting error in postgresql can someone helpe me with that

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 show_emp_details(character varying) line 5 at SQL statement SQL state: 42601

mycode:

CREATE OR REPLACE  function show_emp_details(project_id varchar(10))
RETURNS VARCHAR
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
   show_emp_details VARCHAR;
   BEGIN
   SELECT  emp_id ,role_id,from_date,to_date
FROM allocation al
JOIN t_project pj
ON(pj.project_id=al.project_id);
  
   RETURN
   emp_id ,role_id,from_date,to_date ;
   END
   $BODY$;

drop function show_emp_details();

SELECT show_emp_details('P01');

I need to create a functions

Upvotes: 0

Views: 411

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1630

There are multiple problems with your function:

  1. You declare it to return single varchar field but based on your function body you are trying returning multiple fields. Take a look at Return multiple fields as a record in PostgreSQL with PL/pgSQL
  2. You have SELECT in your function, but it does not have INTO, you do not define where the result should be put. If you wish to return the result of the SQL take a look at https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING - especially at RETURN QUERY.
  3. You don't even need plpgsql for a simple function returning query result. Use sql language for it.
  4. I'm sure you aren't selecting the function after you drop it, just edit the code in your question.

Upvotes: 0

Related Questions