Return Setof Record (Virtual Table) from Function

Return setof record (virtual table) from function

(This is all tested with postgresql 8.3.7-- do you have an earlier version? just looking at your use of "ALIAS FOR $1")

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR $1;
 result RECORD;
BEGIN
 RETURN QUERY SELECT '1', '2', '3';
 RETURN QUERY SELECT '3', '4', '5';
 RETURN QUERY SELECT '3', '4', '5';
END
$$;

If you have a record or row variable to return (instead of a query result), use "RETURN NEXT" rather than "RETURN QUERY".

To invoke the function you need to do something like:

select * from storeopeninghours_tostring(1) f(a text, b text, c text);

So you have to define what you expect the output row schema of the function to be in the query. To avoid that, you can specify output variables in the function definition:

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
 RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
BEGIN
 RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
END
$$;

(not quite sure why the extra ::text casts are required... '1' is a varchar by default maybe?)

SQL function return-type: TABLE vs SETOF records

When returning SETOF record the output columns are not typed and not named. Thus this form can't be used directly in a FROM clause as if it was a subquery or a table.

That is, when issuing:

SELECT * from events_by_type_2('social');

we get this error:

ERROR: a column definition list is required for functions returning
"record"

It can be "casted" into the correct column types by the SQL caller though. This form does work:

SELECT * from events_by_type_2('social') as (id bigint, name text);

and results in:

 id |      name      
----+----------------
  1 | Dance Party
  2 | Happy Hour
 ...

For this reason SETOF record is considered less practical. It should be used only when the column types of the results are not known in advance.

Easy way to have return type be SETOF table plus additional fields?

You could return a whole row as composite type and add some more:

CREATE OR REPLACE FUNCTION f_rowplus()
  RETURNS TABLE (rec demo, add_int int, add_txt text) AS
$func$
SELECT d, 5, 'baz'::text FROM demo d;
$func$  LANGUAGE sql;

But then, when you use the simple call:

SELECT * FROM f_rowplus();

You get the row from table demo as separate composite type. You'd have to call:

SELECT (rec).*,  add_int, add_txt FROM f_rowplus();

to get all individual columns. Parentheses required.

Postgres is a bit inconsistent here. If you create a function with:

CREATE OR REPLACE FUNCTION f_row2()
  RETURNS TABLE (rec demo) AS
...

then the composite type demo is silently converted into individual columns (decomposed). No link to the original composite type remains. You cannot reference the declared output column rec at all, since that has been replaced with the columns of the decomposed type. This call would result in an error message:

SELECT rec FROM f_row2();  -- error!

Same here:

CREATE OR REPLACE FUNCTION f_row3(OUT rec demo)
  RETURNS SETOF demo AS
...

However, as soon as you add any more OUT columns, the composite type is preserved as declared (not decomposed) and you can:

SELECT rec FROM f_rowplus();

with the first function.

db<>fiddle here - demonstrating all variants

_{Old sqlfiddle}

Asides

When using a function returning multiple columns in the FROM list (as table function) and decomposing in the SELECT list like this:

SELECT (rec).* FROM f_rowplus();

... the function is still evaluated once only - while calling and decomposing in the SELECT list directly like this:

SELECT (f_rowplus()).*;  -- also: different result

... would evaluate once for every column in the return type. See:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

In Postgres 14 or later, you can also use standard-SQL syntax:

CREATE OR REPLACE FUNCTION f_rowplus_std()
  RETURNS TABLE (rec demo, add_int int, add_txt text)
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT d, 5, 'baz'::text FROM demo d;
END;

See:

• What does BEGIN ATOMIC ... END mean in a PostgreSQL SQL function / procedure?

Function that returns SETOF record in java = error

Bellninita, consider using a cursor instead. Here are example Java and SQL code that has components similar to what you appear to need. This is similar to the production code used for the same purpose:

protected Fault getFault(Integer dataCode, Integer faultCode,
        GregorianCalendar downloadTime, IFilterEncoder filter, FaultType faultType, boolean verbose) {
    // verbose: flag to display logging statements.
    Fault fault = new Fault(faultCode, 0);
    try {
        // We must be inside a transaction for cursors to work.
        conn.setAutoCommit(false);
        // Procedure call: getFault(integer, text, timestamp, integer)
        proc = conn.prepareCall("{ ? = call getfaultCount(?, ?, ?, ?, ?) }");
        proc.registerOutParameter(1, Types.OTHER);
        proc.setInt(2, dataCode);
        proc.setInt(3, faultCode);
        Timestamp ts = new Timestamp(downloadTime.getTimeInMillis());
        cal.setTimeZone(downloadTime.getTimeZone());
        proc.setTimestamp(4, ts, cal);
        proc.setInt(5, filter.getEncodedFilter());
        proc.setString(6, faultType.toString());
        proc.execute();
        if(verbose) {
            log.logInfo(this.getClass().getName(), "SQL: " + proc.toString());
        }
        results = (ResultSet) proc.getObject(1);
        while (results.next()) {
            //Do something with the results here
        }
    } catch (SQLException e) {
        //Log or handle exceptions here
    }
    return fault;
}

Here is the SQL that is inside the function (aka, Stored Procedure):

CREATE OR REPLACE FUNCTION getfaultcount(_dataCodeid integer, _faultcode integer, _downloadtime timestamp without time zone, _filterbitmap integer, _faulttype text)
  RETURNS refcursor AS
$BODY$
DECLARE mycurs refcursor;
BEGIN 
    OPEN mycurs FOR
    SELECT count(*) as faultcount, _downloadtime as downloadtime
    FROM    fs_fault f
        JOIN download_time d ON f.downloadtimeid = d.id
    WHERE   f.faultcode = _faultcode
        AND f.statusid IN(2, 4)
        AND d.downloadtime = _downloadtime
        AND d.dataCodeid = _dataCodeid 
    GROUP BY f.faultcode
    ;
    RETURN mycurs;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getfaultcount(integer, integer, timestamp without time zone, integer, text) OWNER TO postgres;

How to return a table, rows or record from a function in PostgreSQL 9?

I know that pipelined returns in Oracle does this, so I used that to find 'RETURN NEXT' from plpgsql:

http://www.postgresql.org/message-id/007b01c6dc31$ae395920$0a00a8c0@trivadis.com

Also on grokbase:

http://grokbase.com/t/postgresql/pgsql-performance/069kcttrfr/pipelined-functions-in-postgres

(Edit to add official documentation): http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html

Killer, I will have to make use of this myself.

Editing one more time to add in some demo code (directly from postgresql.org documentation):

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();

How can`RETURN TABLE(columns)` be equivalent to using one or more `OUT` parameters, and marking the function as returning `SETOF` record?

These functions are equivalent:

create type my_type as (id int, str text);

create or replace function function_returning_setof(n int)
returns setof my_type language sql as $$
    select i, i::text
    from generate_series(1, n) i;
$$;

create or replace function function_returning_table(n int)
returns table(id int, str text) language sql as $$
    select i, i::text
    from generate_series(1, n) i;
$$;

create or replace function function_with_out_params(in n int, out id int, out str text)
returns setof record language sql as $$
    select i, i::text
    from generate_series(1, n) i;
$$;

DbFiddle.

Return SETOF rows from PostgreSQL function

Sanitize function

What you currently have can be simplified / sanitized to:

CREATE OR REPLACE FUNCTION func_a (username text = '', databaseobject text = '')
  RETURNS ????
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format ('SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)'
         , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END
         , databaseobject);
END
$func$;

You only need additional instances of BEGIN ... END in the function body to start separate code blocks with their own scope, which is rarely needed.

The standard SQL concatenation operator is ||. + is a "creative" addition of your former vendor.

Don't use CaMeL-case identifiers unless you double-quote them. Best don't use them at all See:

• Are PostgreSQL column names case-sensitive?

varchar(4000) is also tailored to a specific limitation of SQL Server. It has no specific significance in Postgres. Only use varchar(4000) if you actually need a limit of 4000 characters. I would just use text - except that we don't need any variables at all here, after simplifying the function.

If you have not used format(), yet, consult the manual here.

Return type

Now, for your actual question: The return type for a dynamic query can be tricky since SQL requires that to be declared at call time at the latest. If you have a table or view or composite type in your database already matching the column definition list, you can just use that:

CREATE FUNCTION foo()
  RETURNS SETOF my_view AS
...

Else, spell the column definition list with out with (simplest) RETURNS TABLE:

CREATE FUNCTION foo()
  RETURNS TABLE (col1 int, col2 text, ...) AS
...

If you are making the row type up as you go, you can return anonymous records:

CREATE FUNCTION foo()
  RETURNS SETOF record AS
...

But then you have to provide a column definition list with every call, so I hardly ever use that.

I wouldn't use SELECT * to begin with. Use a definitive list of columns to return and declare your return type accordingly:

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS TABLE(col1 int, col2 text, col3 date)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format ($f$SELECT v1.col1, v1.col2, v2.col3
              FROM %s v1 LEFT JOIN %I v2 USING (id)$f$
         , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END
         , databaseobject);
END
$func$;

For completely dynamic queries, consider building the query in your client to begin with, instead of using a function.

You need to understand basics first:

• Refactor a PL/pgSQL function to return the output of various SELECT queries
• PL/pgSQL in the Postgres manual

Then there are more advanced options with polymorphic types, which allow you to pass the return type at call time. More in the last chapter of:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

plgpsql - is it possible to declare setof record or table variable?

I think this simplified SQL function might do what you are looking for:

CREATE FUNCTION permission_cache_update(affected_user_list int[])
  RETURNS void AS
$func$

DELETE FROM permission_cache p
USING (SELECT unnest($1) AS u_id) a
WHERE  p.user_id = a.u_id;

INSERT INTO permission_cache (user_id, permission_id)
SELECT u.user_id, r.permission_id
FROM   user_role u
JOIN   role_permission r USING (role_id)
JOIN  (SELECT unnest($1) AS u_id) a ON a.u_id = u.user_id
UNION
SELECT p.user_id, p.permission_id
FROM   user_permission p
JOIN  (SELECT unnest($1) AS u_id) a ON a.u_id = p.user_id;

$func$ LANGUAGE sql;

Writable CTE (a.k.a. data-modifying CTE) would simplify this tremendously but were only introduced with Postgres 9.1. Once again, your outdated version is in the way.

Referencing parameters by name was only introduced in Postgres 9.2 for SQL functions (earlier for plpgsql). So I use the positional parameter $1.

How to return a value from a function if no value is found

Here is the script I used. As you can see I run PostgreSQL 9.4.1.
I used HeidiSQL to launch the queries.
Are you sure that you correctly updated your function?
I just noted that you used a different function ('player_height') in a comment instead of 'get_height' in you original post.

select version();
-- PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit

delimiter //

CREATE OR REPLACE FUNCTION get_height(firstn VARCHAR, lastn VARCHAR)         
RETURNS FLOAT AS $$
DECLARE
height FLOAT = 0.0;
BEGIN
    SELECT into height AVG(((p.h_feet * 12) + p.h_inches) * 2.54)
    FROM players p
    WHERE p.firstname = firstn AND p.lastname = lastn; 
        return coalesce(height, 0.0);
END;
$$ LANGUAGE plpgsql;

delimiter;

CREATE TABLE players (
     firstname varchar(40),
     lastname  varchar(40),
     h_feet int,
     h_inches int);


insert into players values ('Jimmy', 'Howard', 6, 2);

select * from get_height('Jimmy', 'Howard');
-- gives 187.96

select * from get_height('Random', 'Guy');
-- gives 0

---

Related Topics