Find all occurrences of a string in any column with a specific name in postgresql

Question

I have a Postgresql database with many tables, some of these tables have a column called 'description'. Some of these descriptions contain the word 'dog'. How can I print the tables names for the tables that have the string 'dog' anywhere in the column 'description', case insensitive?

I tried with a script, but it is failing with the error

        $$ LANGUAGE plpgsql
        ERROR:  syntax error at or near "END"
        LINE 16:     END LOOP;**

This is the script:

CREATE OR REPLACE FUNCTION findAllDogsInDescription()
RETURNS VOID
AS $$
DECLARE
    my_row    RECORD;
    a int;
     i boolean;
BEGIN       
    FOR my_row IN 
        SELECT table_name from information_schema.columns where column_name = 'description'
    LOOP
       execute 'select count(*) from ' || my_row.table_name || ' where description ilike ''%dog%'' ' into i;
       if (i > 0) THEN
        raise 'Found a dog in the description of the table %', my_row.table_name;
       END IF
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT findAllDogsInDescription();

csd · Accepted Answer · 2015-05-05 21:01:22Z

1

It's a simple syntax error. You just need a ; after the END IF. And you need to use a (which is an int) instead of i in the execute ... into statement.

CREATE OR REPLACE FUNCTION findAllDogsInDescription()
RETURNS VOID
AS $$
DECLARE
    my_row    RECORD;
    a int;
     i boolean;
BEGIN       
    FOR my_row IN 
        SELECT table_name from information_schema.columns where column_name = 'description'
    LOOP
       execute 'select count(*) from ' || my_row.table_name || ' where description ilike ''%dog%'' ' into a;
       if (a > 0) THEN
        raise 'Found a dog in the description of the table %', my_row.table_name;
       END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT findAllDogsInDescription();

answered May 5, 2015 at 21:01

csd

1,79410 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Find all occurrences of a string in any column with a specific name in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related