postgres functions loses column names

Question

I create a function in the PgAdmin query tool that returns a table with a single column:

create or replace function test_function()
  returns table(a integer) as
...

When I run the query, I get a success message and can see the function added to the list, but the definition is stored as:

create or replace function test_function()
  returns setof integer as
...

This only seems to happen for single column returns. It is causing me problems because if I want to create a statement like:

select t.a from test_function();

I cannot, the column name takes on strange name and I get an error message that the return type has no column a. How can I force this to return a table?

Answer 1

This is a bug in pgAdmin (reported here).

The function definition is stored correctly when you first create it. pgAdmin displays it incorrectly, but calls to the function should still behave as expected.

The problem only arises when you take pgAdmin's CREATE OR REPLACE statement and run it. The recreation will appear to succeed, but the function will fail at runtime due to the missing variable declaration.

As long as you remember to fix the return type every time you recreate the function, you shouldn't have a problem.