CODEWITHSUNDEEP
postgresql
Alexandre Santos
Alexandre Santos

Reputation: 8338

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

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();

Upvotes: 0

Views: 463

Answers (1)

csd
csd

Reputation: 1784

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();

Upvotes: 1

Related Questions