CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Mourad El Ghissassi
Mourad El Ghissassi

Reputation: 37

Debugging a PL/pgSQL function

I am trying to get this PL/pgSQL function to work : 

CREATE OR REPLACE FUNCTION loopcolumns2(tableName TEXT, pourcentage real)
RETURNS void AS $$
DECLARE 
    _name text; 
    missing_percentage real;
BEGIN
    FOR _name IN SELECT column_name from information_schema.columns where table_name=tableName LOOP
        SELECT 100 - (count(_name) * 100) / count(*) INTO missing_percentage FROM tableName;
        IF (missing_percentage > pourcentage)
            THEN ALTER TABLE tableName DROP COLUMN _name;
        END IF;
    END LOOP;
END; $$ LANGUAGE plpgsql;

The goal of the function is to loop through all the columns of a table, a delete the columns where the percentage of missing values is bigger than an input percentage.

I get the following error :

SELECT 100 - (count( $1 ) * 100) / count(*) FROM  $2
                                                  ^
CONTEXT:  SQL statement in PL/PgSQL function "loopcolumns2" near line 6

Upvotes: 0

Views: 79

Answers (1)

Gabriel's Messanger
Gabriel's Messanger

Reputation: 3298

You're trying to SELECT from text stored by tableName vaiable. You cannot do that because Postgres think you want him to select from table named tableName, but probably such table doesn't exist.

You nead to create dynamic query in string and use EXECUTE ... INTO ... statement. Like this:

DECLARE query TEXT;
...

query :=  'SELECT 100 - (count(' || _name::TEXT || ') * 100) / count(*) FROM '
          || tableName::TEXT;

EXECUTE query INTO percentage ;
...

Upvotes: 1

Related Questions