CODEWITHSUNDEEP
postgresqlwhile-loopsyntax-errorplpgsql
IFE
IFE

Reputation: 41

Error on a WHILE loop? PL/pgSQL_postgresv10

I have a problem with a PL/pgSQL script (postgres v10). The goal is simple: list the schemas of a database, the tables for each schema with their number of records + a hash for each table. The problem I have is for large tables where I am 'out of memory'. After some research, I code to do it in batches. However, when testing the code, it tells me the error

ERROR: syntax error at or near "<"
WHILE offset < row_count LOOP

I have looked before/after the loop, nothing to report. And if I comment out the entire loop, the code works, a sign that the loop is the problem. Can you help me? Thank you (you will find the code below).

CREATE OR REPLACE FUNCTION verify_migration()
RETURNS TABLE (schema_name TEXT, table_name TEXT, row_count BIGINT, table_md5 TEXT) AS $$
DECLARE
    schema_rec RECORD;
    table_rec RECORD;
    row_count BIGINT;
    batch_size INTEGER := 10000;
    offset INTEGER := 0;
    table_md5 TEXT;
    batch_md5 TEXT;
BEGIN
    FOR schema_rec IN
        SELECT s.schema_name
        FROM information_schema.schemata s
        WHERE s.schema_name NOT IN ('pg_catalog', 'information_schema')
    LOOP
        FOR table_rec IN
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = schema_rec.schema_name
              AND t.table_type = 'BASE TABLE'
        LOOP
            table_md5 := '';
            offset := 0;
            EXECUTE format('SELECT COUNT(*) FROM %I.%I', schema_rec.schema_name, table_rec.table_name)
            INTO row_count;

            WHILE offset < row_count LOOP
                EXECUTE format('
                    SELECT md5(string_agg(t::text, ''''))
                    FROM (
                        SELECT *
                        FROM %I.%I
                        ORDER BY 1
                        LIMIT %L OFFSET %L
                    ) t', schema_rec.schema_name, table_rec.table_name, batch_size, offset)
                INTO batch_md5;
                
                IF batch_md5 IS NOT NULL THEN
                    table_md5 := md5(COALESCE(table_md5, '') || batch_md5);
                END IF;
                
               offset := offset + batch_size;
            END LOOP;
            
            RETURN QUERY
            SELECT
                schema_rec.schema_name::TEXT,       -- Cast explicite en TEXT
                table_rec.table_name::TEXT,         -- Cast explicite en TEXT
                row_count,                          -- deja un BIGINT
                table_md5;                          -- déjà en type TEXT
        END LOOP;
    END LOOP;
END $$ LANGUAGE plpgsql;

Upvotes: 2

Views: 60

Answers (1)

Mureinik
Mureinik

Reputation: 312106

OFFSET is a reserved word in SQL:

Table C.1. SQL Key Words

Key Word PostgreSQL SQL:2023 SQL:2016 SQL-92
OFFSET reserved, requires AS reserved reserved

You can replace it with a name that isn't a reserved word, such as curr_offset, and you should be OK:

curr_offset := 0;
EXECUTE format('SELECT COUNT(*) FROM %I.%I', schema_rec.schema_name, table_rec.table_name)
INTO row_count;

WHILE curr_offset < row_count LOOP
    -- etc...

Upvotes: 1

Related Questions