How to return more than one result with a sql stored procedure?

Question

I'm trying to create a stored procedure using sql (postgre) which would return multiple titles selected.

After some experimenting I've come to the point where I have a procedure working but only returning the first title.

My procedure look like that : (I use set arguments on idsite and idmodele for testing purpose)

-- Function: select_metacontenu_titre(integer, integer)

-- DROP FUNCTION select_metacontenu_titre(integer, integer);

CREATE OR REPLACE FUNCTION select_metacontenu_titre(
    pidmodele integer,
    pidsite integer)
  RETURNS CHARACTER VARYING AS
$BODY$

DECLARE
  result  CHARACTER VARYING;

BEGIN
    SELECT titre INTO result
    FROM t_metacontenu FULL JOIN t_dossiercontenu
    ON t_metacontenu.iddossiercontenu = t_dossiercontenu.iddossiercontenu
    AND t_metacontenu.idsite = t_dossiercontenu.idsite
    WHERE t_metacontenu.idsite = 78158  
    AND t_dossiercontenu.idmodele = 102;
    RETURN result;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;
ALTER FUNCTION select_metacontenu_titre(integer, integer)
  OWNER TO in01;

It returns this :

But I expect this :

I first wanted to use SETOF but after investigating I think it doesn't fit my needs, I understood that SETOF would cast my return. I then saw TABLE option but couldn't find a way to implement it.

Is using TABLE the way to go here ? Any advice is greatly appreciated.

Thanks for your time :)

Answer 1

You need to use returns table (..).

You also don't need PL/pgSQL for this. A plain SQL function will do just fine:

CREATE OR REPLACE FUNCTION select_metacontenu_titre(pidmodele integer, pidsite integer)
  RETURNS table (title CHARACTER VARYING)  AS
$BODY$
    SELECT titre 
    FROM t_metacontenu 
      FULL JOIN t_dossiercontenu 
        ON t_metacontenu.iddossiercontenu = t_dossiercontenu.iddossiercontenu
       AND t_metacontenu.idsite = t_dossiercontenu.idsite
    WHERE t_metacontenu.idsite = 78158  
      AND t_dossiercontenu.idmodele = 102;
$BODY$
LANGUAGE sql;

With PL/pgSQL this would be nearly the same, except that you need to write return query select ... inside a BEGIN ... END block:

CREATE OR REPLACE FUNCTION select_metacontenu_titre(pidmodele integer, pidsite integer)
  RETURNS table (title CHARACTER VARYING)  AS
$BODY$
BEGIN
  return query
    SELECT titre 
    FROM t_metacontenu 
      FULL JOIN t_dossiercontenu 
        ON t_metacontenu.iddossiercontenu = t_dossiercontenu.iddossiercontenu
       AND t_metacontenu.idsite = t_dossiercontenu.idsite
    WHERE t_metacontenu.idsite = 78158  
      AND t_dossiercontenu.idmodele = 102;
END;      
$BODY$
LANGUAGE plpgsql;

You use that just like a "table":

select *
from select_metacontenu_titre(...);