2

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$
3
  • Why are you storing numbers as strings?
    – user1822
    Commented Sep 1, 2021 at 5:34
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Sep 1, 2021 at 11:00
  • Thats a text field, I just put in numbers for testing.
    – Sumchans
    Commented Sep 1, 2021 at 15:23

2 Answers 2

3

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();
1
  • Thanks a_horse_with_no_name for the detailed explanation.
    – Sumchans
    Commented Sep 1, 2021 at 15:24
0

Drop the into the_record portion of your code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.