Execute dynamic SQL query stored in text variable using Postrgres plpgsql [duplicate]

Question

In the Postgres table named my_table, I want to set all empty strings ('') across all variables to null. I have the following do-block which fails at the execute line.

I am quite new to plpgsql and do not understand why. How can I properly execute the command stored in q?

do language plpgsql
$$
DECLARE
  r record;
  q text;
BEGIN
   for r in
        select table_name, column_name
        from information_schema.columns t
        where t.table_name = 'my_table'
  loop
    q := format('UPDATE %s SET %s = NULL WHERE %s = '''';',
                    r.table_name, r.column_name, r.column_name);
    raise notice 'cleaning column: %', q;
    execute q;  -- this line fails
   end loop;
END;
$$;

PS. Any other hints for better code are also welcome :)

klin · Accepted Answer · 2016-11-16 10:00:51Z

2

For a non-text column (say integer id) the query

UPDATE id SET id = NULL WHERE id = ''

will raise an error as you cannot compare integer id to text.

Use cast to text:

...
q := format('UPDATE %I SET %I = NULL WHERE %I::text = '''';',
            r.table_name, r.column_name, r.column_name);
...

As an alternative you can execute the update query only for text columns, e.g.:

...
    select table_name, column_name
    from information_schema.columns t
    where t.table_name = 'my_table'
    and data_type in ('text', 'character varying')
...

edited Nov 16, 2016 at 10:00

answered Nov 13, 2016 at 11:49

klin

123k15 gold badges241 silver badges263 bronze badges

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

7 Comments

Laurenz Albe Over a year ago

This answer should be extended. First, the column could be defined as NOT NULL, which would cause an error. Second, the function is subject to SQL injection problems as it is written; all you need to do is define a function with a malicious table or column name.

Mark Heckmann Over a year ago

Thanks, @klin, you figured out the problem without me having posted the error. Nice. One Add-on: It appears that raise notice does not print anything until the whole loop has terminated. Is there a way to make it print after every loop cycle?

klin Over a year ago

@MarkHeckmann - notices are raised immediately but the way they are received by a client depends on this client. For example, in psql notices are shown asynchronously.

Erwin Brandstetter Over a year ago

This is a loaded footgun for SQL injection, just like @Laurenz says. Table and column names have to be treated as unsafe user input at all times. It's enough to have the TEMP privilege to smuggle in a temp table called "some_existing_tbl_name; DROP SCHEMA public CASCADE; --". But even if you are not paranoid enough, it's still a bug, because non-standard names need to be escaped anyway.

klin Over a year ago

Yes, I should edit the answer to follow a general rule for educational reasons. I do not see however any guns here. Your example is not justified as you cannot see other people's temporary tables in information_schema. If you start to behave as a paranoid that means you not in control of the situation. I prefer to remain rational. The subject is too wide to consider here, note only that the OP selects a specific own table.

|

Collectives™ on Stack Overflow

Execute dynamic SQL query stored in text variable using Postrgres plpgsql [duplicate]

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related