0

I'm trying to create a function to get a field value from multiple tables in my database. I made script like this:

CREATE OR REPLACE FUNCTION get_all_changes() RETURNS SETOF RECORD AS
$$
DECLARE 
  tblname VARCHAR;
  tblrow RECORD;
  row RECORD;
BEGIN
    FOR tblrow IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' LOOP /*FOREACH tblname IN ARRAY $1 LOOP*/
      RAISE NOTICE 'r: %', tblrow.tablename;
      FOR row IN SELECT MAX("lastUpdate") FROM tblrow.tablename LOOP
          RETURN NEXT row;
      END LOOP;
    END LOOP;
END
$$
LANGUAGE 'plpgsql' ;

SELECT get_all_changes();

But it is not working, everytime it shows this error 

tblrow.tablename" not defined in line "FOR row IN SELECT MAX("lastUpdate") FROM tblrow.tablename LOOP"
Improve this question
1
  • Don't use quotes for the language name. It's an identifier, not a string. The manual says: For backward compatibility, the name can be enclosed by single quotes. But really, you shouldn't. Just LANGUAGE plpgsql. Commented May 11, 2013 at 9:23

3 Answers 3

Reset to default
2

Your inner FOR loop must use the FOR...EXECUTE syntax as shown in the manual:

FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

In your case something along this line:

FOR row IN EXECUTE 'SELECT MAX("lastUpdate") FROM ' || quote_ident(tblrow.tablename) LOOP
   RETURN NEXT row;
END LOOP

The reason for this is explained in the manual somewhere else:

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands (as discussed in Section 39.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided[...]

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Better written as IN EXECUTE format('SELECT max("lastUpdate") FROM %I',tblrow.tablename) LOOP ... or at least use || quote_ident(tblrow.tblname). Otherwise bang on.
@CraigRinger: TNX for pointing out. I fixed the answer using quote_ident, since format exists only since 9.1.
0

Answer to your new question (posted as answer):

This can be much simpler. You do not need to create a table just do define a record type. If at all, you would better create a type with CREATE TYPE, but that only pays if you need the type in multiple places. For just a single function, use RETURNS TABLE instead:

CREATE OR REPLACE FUNCTION get_all_changes(text[])
  RETURNS TABLE (
    tablename text
  , "lastUpdate" timestamptz
  , nums int
  )
  LANGUAGE plpgsql AS
$func$
DECLARE 
   tblname text;
BEGIN
   FOREACH tblname IN ARRAY $1
   LOOP
      RETURN QUERY EXECUTE format(
         $f$SELECT '%I', max("lastUpdate"), count(*)::int FROM %1$I$f$
       , tblname)
   END LOOP;
END
$func$;

A couple more points

  • Use RETURN QUERY EXECUTE instead of the nested loop. Much simpler and faster.

  • Column aliases would only serve as documentation, those names are discarded in favor of the names declared in the RETURNS clause (directly or indirectly).

  • Use format() with %I to replace the concatenation with quote_ident() and %1$I to refer to the same parameter another time.

  • count() usually returns type bigint. Cast the integer, since you defined the column in the return type as such: count(*)::int.

Improve this answer

2 Comments

Erwin, Thank you for help. I finally use CREATE TYPE instead of table. It will raise an error when use 'RETURN QUERY', I don't know why.
Create a new question. If it helps, add a link to this one for context. In the new question, provide your function definition, your version of PostgreSQL and the exact error message. You can run SET lc_messages = 'C' to get default English error messages if you are running a different locale.
0

Thanks, I finally made my script like:

CREATE TABLE IF NOT EXISTS __rsdb_changes (tablename text,"lastUpdate" timestamp with time zone, nums bigint);
    CREATE OR REPLACE FUNCTION get_all_changes(varchar[]) RETURNS SETOF __rsdb_changes AS  /*TABLE (tablename varchar(40),"lastUpdate" timestamp with time zone, nums integer)*/
    $$
    DECLARE 
      tblname VARCHAR;
      tblrow RECORD;
      row RECORD;
    BEGIN
        FOREACH tblname IN ARRAY $1 LOOP
          /*RAISE NOTICE 'r: %', tblrow.tablename;*/
          FOR row IN EXECUTE 'SELECT CONCAT('''|| quote_ident(tblname) ||''') AS tablename, MAX("lastUpdate") AS "lastUpdate",COUNT(*) AS nums FROM ' || quote_ident(tblname) LOOP
            /*RAISE NOTICE 'row.tablename: %',row.tablename;*/
            /*RAISE NOTICE 'row.lastUpdate: %',row."lastUpdate";*/
            /*RAISE NOTICE 'row.nums: %',row.nums;*/
            RETURN NEXT row;
          END LOOP;
        END LOOP;
        RETURN;
    END
    $$
    LANGUAGE 'plpgsql' ;

Well, it works. But it seems I can only create a table to define the return structure instead of just RETURNS SETOF RECORD. Am I right?

Thanks again.

Improve this answer

1 Comment

Did you read my comment about quoting the language name? Also, this is not an answer, but a new question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.