RETURN TABLE is converted to RETURN SETOF int in Postgres function

In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE:

CREATE OR REPLACE FUNCTION test(
    IN parm1 date,
    IN parm2 interval)
  RETURNS TABLE(identifier INT) 
AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql;

After the function is created, pgAdminIII shows the function as having the following definition:

CREATE OR REPLACE FUNCTION test(IN parm1 date, IN parm2 interval)
  RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

When executing the function it follows the logic of the RETURNS TABLE. But, when editing the function it will pickup the RETURNS SETOF. This provides different functionality than the function I specified (e.g. the name of the returned column no longer exists).

So, how can I force pgAdminIII to keep the returned column name of identifier? I don't particularly care whether I define it as a TABLE or SETOF.

What you see is obviously a display bug in the current pgAdmin 1.20. I could reproduce it.

You must be aware that the code you see is re-engineered from system table entries.

If you look up your function in the system catalogs directly, you'll find that your the return type has been registered properly (at least it works for me in pg 9.4):

SELECT pg_get_functiondef(oid)
FROM   pg_proc
WHERE  proname = 'test';

More useful functions like pg_get_functiondef() in the manual.

You might want to ask for this on the pgAdmin support list:

http://www.postgresql.org/list/pgadmin-support/

Mail to pgadmin-support@postgresql.org


How can I force the returned column to have a name of identifier?

Your approach with RETURNS TABLE(identifier int) is the right way. RETURNS SETOF does not take a parameter name, unless you combine it with an OUT parameter. Like so:

CREATE OR REPLACE FUNCTION test(parm1 date
                              , parm2 interval
                              , OUT identifier int)
  RETURNS SETOF integer AS ...

StackExchange Database Administrators Q#105920, answer score: 3