1

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

  • 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 Dec 6 '15 at 5:22
2

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
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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