current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
    
One more thing: It hardly makes sense to define the input parameter as character varying(30). Just use varchar (without maximum length) or text. – Erwin Brandstetter 10 hours ago

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
Thanks, that solved the trick. – Welten Schann 22 hours ago
up vote 3 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.