CODEWITHSUNDEEP
functionrecordpostgresql-9.1
Ram
Ram

Reputation: 815

PostgreSQL function return type record showing error

I have below table and function.function created successfully but when i call the function,it is showing error.

create table emp_details(empno int,ename varchar(20),sal numeric(7,2))

insert into emp_details values(101,'John',4500)
insert into emp_details values(101,'david',5000)

CREATE OR REPLACE FUNCTION test_select(IN eno integer)
RETURNS SETOF RECORD AS                                                                                   
$body$
BEGIN
        RETURN QUERY                                                       
        SELECT ename,sal FROM EMP_DETAILS WHERE EMPNO=eno;
END;
$body$
LANGUAGE plpgsql

function created successfully.But while calling the function.it is showing error.how can we achieve in record type?

select test_select(101)

Upvotes: 0

Views: 143

Answers (1)

user330315
user330315

Reputation:

Use returns table instead, that way you don't need to specify the column names when using the function. And you don't need PL/pgSQL for this, a language sql function is much more efficient if you just want to wrap a query.

CREATE OR REPLACE FUNCTION test_select(IN eno integer)
  RETURNS TABLE (ename text, sal numeric)
AS                                                                                   
$body$
  SELECT ename,sal 
  FROM emp_details
  WHERE EMPNO=eno;
END;
$body$
LANGUAGE sql;

Set returning functions (regardless if defined as returns setof or with returns table) need to be used in the FROM clause, so you need to use it like this:

select *
from test_select(101);

Upvotes: 1

Related Questions