PosgtreSQL procedure

I am new to postgres function. My requirement is to run a function on each and every table present in particular schema in postgres database but I am facing a problem because the function is returning only a single record each time the function calls while my query should returns "n" number of records for each table the function calls. Ahh it might be confusing- See code-

create or replace function name_list(schema text, tablename text) 
  returns text
as $body$
declare
  result text;
  query text;
begin
  query := 'SELECT "names"  FROM ' || schema || '.' || tablename;
  RAISE NOTICE '"%"' , query;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

copy(select 
  table_name, 
  name_list(table_schema, table_name)
  from information_schema.tables) to 'C:\test\name_list.csv' DELIMITER ',' CSV HEADER;

I read few of the things like set of, loop and tried to execute but still no luck. Any help would be appreciated.

Solution

your function must be a set-returning

create or replace function name_list(schemaname text, tablename text) 
  -- function will return a set
  RETURNS SETOF text
language 'plpgsql'
as $body$
declare
  _query text;
begin
  -- correct way to format the dynamic sql
  _query := FORMAT('SELECT "names" FROM %I.%I;', schemaname, tablename);
  RAISE NOTICE '"%"' , _query;
  -- execute and return all
  RETURN QUERY EXECUTE _query;
end;
$body$;