mardi 9 mars 2021

How do I exclude a table in loop that updates all tables pgsql

I am trying to update all tables by setting the id field as the primary key. I have one table that does not have an id column, and need to skip it. This is what I have so far, but can't seem to get the query to run:

DECLARE
    rec record;
BEGIN
    FOR rec IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns 
        WHERE column_name = 'id'
                if not FOUND then
                     raise notice'no id found' 
                end if; END LOOP

    LOOP
        EXECUTE format('ALTER TABLE %I.%I ADD PRIMARY KEY (id)',
            rec.table_schema, rec.table_name, rec.column_name);
    END LOOP;
END;
$$

I keep getting this error:

ERROR:  missing "LOOP" at end of SQL expression
LINE 12:     end if; END LOOP; 

Where is the END LOOP suppose to go, if not right after the if case? And is this event he right way to go about mass updating primary keys? Also, inside the if case, how do I make it so that the table is skipped rather than returning a notice?

Aucun commentaire:

Enregistrer un commentaire