SQL Function Return-Type: Table VS Setof Records

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?

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

How to return type table in postgres?

As per postgres documentation

SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. In the simple (non-set) case, the first row of the last query's result will be returned. (Bear in mind that “the first row” of a multirow result is not well-defined unless you use ORDER BY.) If the last query happens to return no rows at all, the null value will be returned.

Alternatively, an SQL function can be declared to return a set (that is, multiple rows) by specifying the function's return type as SETOF sometype, or equivalently by declaring it as RETURNS TABLE(columns). In this case all rows of the last query's result are returned. Further details appear below.

You'll need to use set_of. just substitute in your code

RETURNS PARTOK_JOB

with

RETURNS setof PARTOK_JOB

The whole function definition should be

CREATE OR REPLACE FUNCTION get_partok(
in_col1 VARCHAR(40)
)
RETURNS setof PARTOK_JOB

LANGUAGE sql
AS
$$
SELECT * FROM partok_job WHERE col1 = in_col1 ;
$$
;

after making this change, when selecting

SELECT * FROM get_partok('one');

the output is

 col1 | col2  | expiration | last_update | date 
------+-------+------------+-------------+------
 one  | test1 | nothing    |           0 | 
 one  | test2 | nothing    |           0 | 
 one  | test3 | nothing    |           0 | 
(3 rows)

Return multiple columns and rows from a function PostgreSQL instead of record

I was able to see it as expected with this query:

SELECT * FROM brand_hierarchy (id)

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

PostgreSQL call function returning setof record with table and additional columns

If the query in your function should return a my_table_name (a “whole-row reference), you should write it like

SELECT mtn, count(*) OVER () ...

How to return diferent table data based on an ID passed to an SQL function

This is generally impossible with SQL functions. Even with a polymorphic return type, the actual return type must be determined at call time. But all statements in an SQL function are planned before the function is executed. So you'd always end up with an error message for one of the SELECT statements returning data that doesn't fit the return type.

The same can be done with dynamic SQL in a PL/pgSQL function - with some trickery:

CREATE OR REPLACE FUNCTION f_demo(_tabletype anyelement, _id int)
  RETURNS SETOF anyelement LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
      format('SELECT * FROM %s WHERE id = $1', pg_typeof(_tabletype))
   USING  _id;
END
$func$;

Call (important!):

SELECT * FROM f_demo(null::schemaZ.Table_1, 1);

The "trick" is to cast a null value to the desired table type, thereby defining the return type and choosing from which table to select. Detailed explanation:

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

Take this as proof of concept. Typically, there are better (safer, less confusing, more performant) solutions ...

Related:

• Difference between language sql and language plpgsql in PostgreSQL functions

---

Related Topics