Returning result set from Postgres functions

Question

In my Postgres 9.2 database, I need to build a function that takes several parameters, performs several queries, and then returns a data set that is composed of several rows and several columns. I've built several test functions to get a better grasp of Postgres' functionality, here is one:

CREATE OR REPLACE FUNCTION sql_with_rows11(id integer) RETURNS character varying AS
$BODY$
 declare vid integer;
 declare vendor character varying;
BEGIN
 vid := (select v_id from public.gc_alerts where a_id = id);
 vendor := (select v_name from public.gc_vendors where v_id = vid);
 
 RETURN vendor;
END;
$BODY$
   LANGUAGE plpgsql;

I know that I can combine this into one query, but this is more of a practice exercise. This works fine and I get the vendor name. However, I need to return more than one column from the gc_vendors table.

Ultimately, I need to return columns from several tables based on subqueries. I've looked into creating a result set function, but I believe it only returns one row at a time. I also looked into returning setof type, but that seems to be limited to existing tables.

After initial feedback, I changed the function to the following:

CREATE OR REPLACE FUNCTION sql_with_rows14(IN v_uid character varying, IN lid           integer) 
 RETURNS table (aid int, aname character varying) AS
$BODY$
 declare aid integer;
 declare aname character varying;
BEGIN
 sql_with_rows14.aid := (select a_id from public.gc_alerts where v_id =           sql_with_rows14.v_uid);
 sql_with_rows14.aname := (select a_name from public.gc_alerts where a_id =      sql_with_rows14.aid);

 RETURN;
END;
$BODY$
   LANGUAGE plpgsql;

I also tried RETURN NEXT, but same results.

When I query it, if the query returns only one row, it works fine. However it doesn't work for multiple rows. I also tried something like this, with the same result:

...
BEGIN
 sql_with_rows14.aid := (select a_id from public.gc_alerts);
 sql_with_rows14.aname := (select a_name from public.gc_alerts);
 
 RETURN NEXT;
END;

Answer 1

First of all, consider using views or simple queries. I'd say that if you can process something with a simple query, you shouldn't create function for that. in your case, you can use this query

select
    v.v_name, v.* -- or any other columns from gc_alerts or gc_vendors
from public.gc_alerts as a
    inner join public.gc_vendors as v on v.v_id = a.vid
where a.a_id = <your id here>

if you want your function to return rows, you can declare it like

CREATE OR REPLACE FUNCTION sql_with_rows11(id integer)
RETURNS table(vendor text, v_id int)
as
$$
    select
        v.v_name, v.v_id
    from public.gc_alerts as a
        inner join public.gc_vendors as v on v.v_id = a.vid
    where a.a_id = id
$$ language SQL;

or plpgsql function:

CREATE OR REPLACE FUNCTION sql_with_rows11(id integer)
RETURNS table(vendor text, vid int)
AS
$$
    declare vid integer;
    declare vendor character varying;
BEGIN
    sql_with_rows11.vid := 1; -- prefix with function name because otherwise it would be declared variables
    sql_with_rows11.vendor := 4;
    return next; 

    sql_with_rows11.vid := 5;
    sql_with_rows11.vendor := 8;
    return next;
END;
$$ LANGUAGE plpgsql;

sql fiddle demo to fiddle with :)

Answer 2

I need to return more than one column from the gc_vendors table

To return a single row with multiple fields (as opposed to a set of rows), you can either use:

RETURNS row_type

.. where row_type is a pre-defined composite type (like a table name, that serves as such automatically). Or:

RETURNS record

combined with OUT parameters. Be aware that OUT parameters are visible in the body almost everywhere and avoid naming conflicts.
Using the second option, your function could look like this:

CREATE OR REPLACE FUNCTION sql_with_columns(IN  _id integer   -- IN is optional default
                                          , OUT vid integer
                                          , OUT vendor text)
  RETURNS record
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT INTO vid  v_id
   FROM   public.gc_alerts
   WHERE  a_id = id;

   SELECT INTO vendor  v_name
   FROM   public.gc_vendors
   WHERE  v_id = vid;

   RETURN;     -- just noise, since OUT parameters are returned automatically
END
$func$;

As you mentioned, you should combine both queries into one, or even use a plain SQL statement instead. This is just a show case. The excellent manual has all the details.

You can also use:

RETURNS TABLE (...)

Or:

RETURNS SETOF row_type

This allows to return a set of rows (0, 1 or many). But that's not in your question.

To get individual columns instead of a record representation, call the function with:

SELECT * FROM sql_with_columns(...);

There are lots of examples here on SO, try a search - maybe with additional key words.

Also read the chapter "Returning from a Function" in the manual.