How to return uncertain number columns of a table from a postgresql function?

Question

As we know, plpgsql functions can return a table like this:

RETURNS table(int, char(1), ...)

But how to write this function, when the list of columns is uncertain at the time of creating the function.

Answer 1

When a function returns anonymous records

RETURNS SETOF record

you have to provide a column definition list when calling it with SELECT * FROM. SQL demands to know column names and types to interpret *. For registered tables and types this is provided by the system catalog. For functions you need to declare it yourself one way or the other. Either in the function definition or in the call. The call could look like @Craig already provided. You probably didn't read his answer carefully enough.

Depending on what you need exactly, there are a number of ways around this, though:

1) Return a single anonymous record

Example:

CREATE OR REPLACE FUNCTION myfunc_single()  -- return a single anon rec
  RETURNS record AS
$func$
DECLARE
   rec record;
BEGIN
   SELECT into rec  1, 'foo';  -- note missing type for 'foo'
   RETURN rec;
END
$func$  LANGUAGE plpgsql;

This is a very limited niche. Only works for a single anonymous record from a function defined with:

RETURNS record

Call without * FROM:

SELECT myfunc_single();

Won't work for a SRF (set-returning function) and only returns a string representation of the whole record (type record). Rarely useful.
To get individual cols from a single anonymous record, you need to provide a column definition list again:

SELECT * FROM myfunc_single() AS (id int, txt unknown); -- note "unknown" type

2) Return well known row type with a super-set of columns

Example:

CREATE  TABLE t (id int, txt text, the_date date);
INSERT INTO t VALUES  (3, 'foz', '2014-01-13'), (4, 'baz', '2014-01-14');

CREATE OR REPLACE FUNCTION myfunc_tbl()  -- return well known table
  RETURNS SETOF t AS
$func$
BEGIN
   RETURN QUERY
   TABLE t;
   -- SELECT * FROM t;  -- equivalent
END
$func$  LANGUAGE plpgsql;

The function returns all columns of the table. This is short and simple and performance won't suffer as long as your table doesn't hold a huge number of columns or huge columns.

Select individual columns on call:

SELECT id, txt FROM myfunc_tbl();
SELECT id, the_date FROM myfunc_tbl();

-> SQLfiddle demonstrating all.

3) Advanced solutions

This answer is long enough already. And this closely related answer has it all:
Refactor a PL/pgSQL function to return the output of various SELECT queries

Look to the last chapter in particular: Various complete table types

Answer 2

If the result is of uncertain/undefined format you must use RETURNS record or (for a multi-row result) RETURNS SETOF record.

The calling function must then specify the table format, eg:

SELECT my_func() AS result(a integer, b char(1));

BTW, char is an awful data type with insane space-padding rules that date back to the days of fixed-width file formats. Don't use it. Always just use text or varchar.

Given comments, let's make this really explicit:

regress=> CREATE OR REPLACE FUNCTION f_something() RETURNS SETOF record AS $$
SELECT 1, 2, TEXT 'a';
$$ LANGUAGE SQL;
CREATE FUNCTION

regress=> SELECT * FROM f_something();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM f_something();

regress=> SELECT * FROM f_something() AS x(a integer, b integer, c text);
 a | b | c 
---+---+---
 1 | 2 | a
(1 row)