Return data from Postgres functions

Question

I am new to PostgreSQL and I am running into a few problems getting it to do what I want.

I need to build a function that would accept several variables, perform several queries internally and then return 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 VOLATILE
   COST 100;
 ALTER FUNCTION sql_with_rows11(integer)
   OWNER TO postgres;

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, now I need to return more than one column from the gc_vendors table.

In end, I will need to build a function that will return columns from several tables based on subqueries. I've looked into creating a resultset 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 a table that exists.

--------------EDIT

Hey guys, thanks for the help. 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 VOLATILE

I also tried it with 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 same results. Can you guys help again?

Tried this too: 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

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

To return a single row with multiple columns (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   -- the IN key word is just noise
    , OUT vid integer
    , OUT vendor text
    ) RETURNS record 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$ LANGUAGE plpgsql

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 would allow you 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 a function like that with:

SELECT * FROM sql_with_columns(...)

There are lots of examples here on SO, try a search with the plpgsql tag and some key words.

While working with plpgsql, also read the chapter on how to return from a function in the manual.

Answer 2

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 :)