CODEWITHSUNDEEP
postgresqlplpgsql
Mingcai SHEN
Mingcai SHEN

Reputation: 117

Create a function to get column from multiple tables in PostgreSQL

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"

Upvotes: 0

Views: 1825

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Answer to your new question (mislabeled 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's only efficient if you need the type in multiple places. For just a single function, you can use RETURNS TABLE instead :

CREATE OR REPLACE FUNCTION get_all_changes(text[])
 RETURNS TABLE (tablename text
               ,"lastUpdate" timestamp with time zone
               ,nums integer) 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$ LANGUAGE plpgsql;

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.

Upvotes: 0

Mingcai SHEN
Mingcai SHEN

Reputation: 117

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.

Upvotes: 0

A.H.
A.H.

Reputation: 66243

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[...]

Upvotes: 2

Related Questions