Postgresql - Error in function that returns table

Question

I just defined this function to return all column names from a given table:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
returns table(colunas character varying) as $$
begin
 SELECT column_name
 FROM information_schema.columns
 WHERE table_schema = 'Main'
 AND table_name = tbl_name;
end;
$$ language 'plpgsql'

But When I call it using select * from get_columns('tabfuncionarios'); I just got the following error:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function get_columns(character varying) line 3 at SQL statement

I'm using postgresql 9.4.5 version

• Erwin Brandstetter: One more thing: It hardly makes sense to define the input parameter as character varying(30). Just use varchar (without maximum length) or text.

Answer 1

You just need to add RETURN QUERY to the start of your query:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
returns table(colunas character varying) as $$
begin
 RETURN QUERY
 SELECT column_name
 FROM information_schema.columns
 WHERE table_schema = 'Main'
 AND table_name = tbl_name;
end;
$$ language plpgsql

Answer 2

You don't need plpgsql for this. A plain sql function will do:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
 returns table(colunas character varying) 
as 
$$
 SELECT column_name
 FROM information_schema.columns
 WHERE table_schema = 'Main'
 AND table_name = tbl_name;
$$ 
language sql;

Additionally: the language name is an identifier. Don't put it in single quotes.