CODEWITHSUNDEEP
postgresqlplpgsql
Tim Duncklee
Tim Duncklee

Reputation: 1420

How can I use the result of PostgreSQL INFORMATION_SCHEMA in PL/pgSQL?

I am trying to write a generic stored function that queries the INFORMATION_SCHEMA and uses that information to access the actual field value of the table described by INFORMATION_SCHEMA. Here is a snippet of code that shows what I am trying to do:

select_sql := 'SELECT * FROM "' || tableName || '" WHERE "' || tablePKey || '" = ''' || key_id || ''';';
FOR existing_rec IN EXECUTE select_sql LOOP
    describe_sql := 'SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE table_name = ''' || tableName || ''';';
    FOR describe_rec IN EXECUTE describe_sql LOOP
        RAISE NOTICE 'table: % field: % value: %', 
                      tableName, 
                      describe_rec.column_name, 
 This is my issue ->  existing_rec."describe_rec.column_name"; <- This is my issue.
    END LOOP;
END LOOP;

How can I get the actual value of the fields in existing_rec from the outer loop using the field names retrieved from the INFORMATION_SCHEMA?

Upvotes: 2

Views: 75

Answers (1)

Tom-db
Tom-db

Reputation: 6868

If I understand right, you need the content of the tables. This is not stored in any variable but you can query the table and store the result in a new variable:

-- Add this line:
DECLARE _content RECORD;
....
FOR existing_rec IN EXECUTE select_sql LOOP
    describe_sql := 'SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE table_name = ''' || tableName || ''';';
    FOR describe_rec IN EXECUTE describe_sql LOOP

        -- Add following statement:
        EXECUTE format('SELECT %I FROM %I',
                         describe_rec.column_name,
                         _table) INTO _content;

        RAISE NOTICE 'table: % field: % value: %', 
                      tableName, 
                      describe_rec.column_name, 
                      _content;
    END LOOP;
END LOOP;

Upvotes: 1

Related Questions