Postgres function not returning any rows but no errors

Question

I have this below function which returns no rows, but no errors as well. Am I missing any statement over here to return the rows?. If I execute the select statement alone I do get results.

CREATE OR REPLACE FUNCTION public.create_new_conversation()
 RETURNS TABLE(chat_id text, members jsonb)
 LANGUAGE plpgsql
AS $function$
declare
   the_record record;
begin
   select conversation_id, participants
   into the_record
   from public.conversations
   where conversation_id='123456';
   
--    return conversationid;
end;
$function$

Answer 1

You need to use return query inside a PL/pgSQL function to return the result of a query (as a table). There is no need to store the row into an intermediate result:

CREATE OR REPLACE FUNCTION public.create_new_conversation()
 RETURNS TABLE(chat_id text, members jsonb)
 LANGUAGE plpgsql
AS $function$
begin
   return query
     select conversation_id, participants
     from public.conversations
     where conversation_id='123456';
end;
$function$

But to wrap a SQL query into a function, no PL/pgSQL is needed. A plain SQL function will be more efficient:

CREATE OR REPLACE FUNCTION public.create_new_conversation()
 RETURNS TABLE(chat_id text, members jsonb)
 LANGUAGE sql
 STABLE
AS $function$
   select conversation_id, participants
   from public.conversations
   where conversation_id='123456';
$function$

As the function is defined as returns table you also have to make sure you use it like a table:

select *
from create_new_conversation();

Answer 2

Drop the into the_record portion of your code.