SETOF function

[rustkas]

Using interface of Supabase "/database/functions" we can an option to create a function. I would create of function which return several row. As I understand it is SETOF function type. How to write definition of a function to get such type of results?

For instance, I have a function definition.

DECLARE
  user_record record;
BEGIN
	SELECT d.name AS deck_name, bc.title, bc.text, bc.translation, bc.transcription, bc.topics,
	   bc.items, bc.hints, bc.audio, bc.video,
	   bc.deck_id, bc.examples, bc.info, 'blank' as card_type 
	INTO user_record
	FROM public.blank_cards bc
	JOIN public.decks d ON bc.deck_id = d.id
	LEFT JOIN public.user_cards uc ON bc.id = uc.card_id AND uc.card_type = 'blank' AND uc.user_id = $1 AND uc.hidden = false;

  RETURN row_to_json(user_record);
END;

I would like to get several rows. What I need to add into this definition to achieve desired result?

[rustkas]

I far as I understand Supabase does not support returning a SETOF record directly from a PostgreSQL function. Instead, we can modify the function to return a table, which can be queried using Supabase.

CREATE or REPLACE FUNCTION get_blank_cards2(user_id uuid) RETURNS TABLE (
  deck_name character varying,
  title character varying,
  text character varying,
  translation character varying,
  topics JSON,
  items JSON,
  hints character varying[],
  audio character varying[],
  video JSON[],
  examples JSON,
  info text,
  card_type public.card_type 
) AS $$
DECLARE
  current_card_type CONSTANT public.card_type := 'blank';
BEGIN
	RETURN QUERY
		SELECT d.name AS deck_name, bc.title, bc.text, bc.translation, bc.topics, bc.items, bc.hints, bc.audio, bc.video, bc.examples, bc.info, current_card_type as card_type
		FROM public.blank_cards bc
		JOIN public.decks d ON bc.deck_id = d.id
		LEFT JOIN public.user_cards uc ON bc.id = uc.card_id AND uc.card_type = current_card_type AND uc.user_id = $1 AND uc.hidden = false;
END;
$$ LANGUAGE plpgsql;

We need to run this code in "SQL Editor". This code create function which return table record as set of selected data.

This is working solution.