Strange 'Unknown table' when performing plpgsql script

Question

I have one same table in several schemas from PostgreSQL database server. I need execute one query like below:

CREATE OR REPLACE FUNCTION git_search() RETURNS SETOF git_log AS $$
DECLARE
sch name;
BEGIN
    FOREACH sch IN 
     select schema_name from information_schema.schemata where schema_name not in ('pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','information_schema') 
    LOOP 
        qry := 'select count(*) from'|| quote_ident(sch) || '.git_log gl where gl.author_contributor_id = 17';      
    RETURN QUERY qry; 
    END LOOP;
    RETURN;
END;    
$$ LANGUAGE plpgsql; 

select git_search();

but I have the error:

ERROR: "git_log" type not exists
SQL state: 42704

The git_log table is unknown in the first line of script. (clause CREATE)

Anybody can help me?

There are more than 100 schemas where I need perform the query that is adjusted for this situation. What is the best way to do this? Where I can create the function for this purpose?

Presumably `git_log` is not a known type in the schema where the function is being created. — Gordon Linoff, Aug 22 '14 at 21:22
@GordonLinoff There are more than 100 schemas where i need perform the query that is adjusted for this situation. What is the best way to do this? Where i can create the function for this purpose? — Tarcisio M., Aug 22 '14 at 21:38
You need to define a type that is either a string (for the way the function is currently written) or an integer (assuming you really mean return query execute). — Gordon Linoff, Aug 22 '14 at 22:51
Sorry to say, but Gordon's comments are *completely incorrect*. The schema you are creating the function in has nothing to do with anything here. You don't have to define more types, certainly not `a type that is either a string .. or an integer`. Please ignore that. I posted a comprehensive answer. — Erwin Brandstetter, Aug 24 '14 at 01:04
@ErwinBrandstetter thank you for help, now the script is functional. — Tarcisio M., Aug 24 '14 at 23:03

score 1 · Accepted Answer · edited May 23 '17 at 10:25

The table name would serve just fine as composite type name, because a composite type of the same name (schema-qualified) is created with every table automatically.

The immediate cause of the error: none of your tables (actually the associated composite type of the same name) named git_log can be found in the current search_path, so the type name cannot be resolved.

Since you are operating with many schemas and many instances of tables called git_log, you need to be unambiguous and schema-qualify the table name. Just pick any one of your tables in one of the schemas, they all share the same layout:

But the rest of your function isn't going to work either. It's not a "plpgsql script", but a function definition. Try this:

CREATE OR REPLACE FUNCTION git_search()
  RETURNS SETOF one_schema.git_log AS
$func$
DECLARE
   sch text;
BEGIN
   FOR sch IN 
      SELECT schema_name
      FROM   information_schema.schemata
      WHERE  schema_name NOT LIKE 'pg_%'
      AND    schema_name <> 'information_schema'
      ORDER  BY schema_name
   LOOP 
      RETURN QUERY EXECUTE format(
         'SELECT count(*)
          FROM   %I.git_log
          WHERE  author_contributor_id = 17', sch);      
   END LOOP;
END  
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM git_search();

Major points

FOREACH is for looping though arrays, you need a FOR loop.
You need dynamic SQL. Search for examples with more explanation.
Call the function with SELECT * FROM.
Related answer (one of many):
- Passing column names dynamically for a record variable in PostgreSQL

In the final code, my function returns 'SETOF record' instead 'one_schema.git_log' and the context of the call I had adjust to: SELECT * FROM git_search () AS foo (val bigint); — Tarcisio M., Aug 24 '14 at 23:21
@TarcisioM.: I hardly ever return anonymous records with `RETURNS SETOF record`. Postgres can not decompose the records and you have to provide a column definition list with every call. [If you know the return type, declare it.](http://stackoverflow.com/questions/11907563/declaring-the-tuple-structure-of-a-record-in-pl-pgsql/11911949#11911949) Also, [if you have your answer, please accept it.](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) — Erwin Brandstetter, Aug 25 '14 at 01:06

Strange 'Unknown table' when performing plpgsql script

1 Answers1

Major points