PostgreSQL – How to Fix Error in Function That Returns Table

plpgsqlpostgresql

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

Best Answer

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
Related Question