Debugging a PL/pgSQL function

Question

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

Gabriel's Messanger · Accepted Answer · 2016-04-18 12:44:58Z

1

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 ;
...

edited Apr 18, 2016 at 12:44

answered Apr 18, 2016 at 12:30

Gabriel's Messanger

3,37822 silver badges32 bronze badges

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

1 Comment

Mourad El Ghissassi Over a year ago

Can yo ugive me mre details please ? How can I still use _name and table_name as variables inside the EXECUTE ? Thank you

Collectives™ on Stack Overflow

Debugging a PL/pgSQL function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related