HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to create function with Return Type as (TABLE student_name string)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
student_namecreatewithreturnfunctiontypehowstringtable

Problem

I am creating function using pgAdmin4 and the CODE Basically something like

BEGIN
   RETURN QUERY
    SELECT * FROM teest."students";
END


I am just wondering, how do I set the TABLE ReturnType as
TABLE(applicant_id integer, ship_id integer) something like that. There's no TABLE return type there.

Thanks

Solution

Either by using returns table

create function get_students()
 returns table(applicant_id integer, ship_id integer)
as
$
  select applicant_id, ship_id
  from test.students
$
language sql;


create function get_student_names()
 returns table(student_name text)
as
$
  select name
  from test.students
$
language sql;


Or by using setof

create function get_students
 returns setof students
as
$
  select *
  from test.students
$
language sql;

Code Snippets

create function get_students()
 returns table(applicant_id integer, ship_id integer)
as
$$
  select applicant_id, ship_id
  from test.students
$$
language sql;
create function get_student_names()
 returns table(student_name text)
as
$$
  select name
  from test.students
$$
language sql;
create function get_students
 returns setof students
as
$$
  select *
  from test.students
$$
language sql;

Context

StackExchange Database Administrators Q#195350, answer score: 2

Revisions (0)

No revisions yet.