Update from stored procedure

Question

I need to create a stored procedure which can take in the schema name and table name as the parameter and make an update on the same table.

CREATE OR REPLACE FUNCTION garscratch.update_table(schema_name text, table_name text ) RETURNS void as $$
DECLARE
 table TEXT;
BEGIN
execute 'update '||schema||'.'||table_name|| 'set id = substring(id from 1 for 2) where name = "test"';
END;
$$ LANGUAGE plpgsql;

When I execute the procedure above as:

select update_table(my,my_table);

I get the error:

Column "my" does not exist.

It does not treat "my" as the schema name.

@Your: This clearly concerns SQL injection. Identifiers have to be treated as user input and sanitized for dynamic SQL. That's actually the most important problem here. A missing blank is easily solved. — Erwin Brandstetter, Apr 10 '14 at 14:26

score 2 · Accepted Answer · edited May 23 '17 at 10:33

You'd need a space between tablename and set.
You need sinlge quotes around the value 'test'.
Or if it's supposed to be a column name, you don't need quotes at all.
You need to sanitize identifiers to avoid SQL injection.

Use instead (complete rewrite):

CREATE OR REPLACE FUNCTION garscratch.update_table(_tbl regclass)
  RETURNS void AS
$func$
BEGIN
EXECUTE 'UPDATE ' || _tbl || $$ SET id = left(id, 2) WHERE name = 'test'$$;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT garscratch.update_table('myschema.my_table');

Detailed explanation:
Table name as a PostgreSQL function parameter

score 1 · Answer 2 · answered Apr 10 '14 at 14:10

1

It seems to me you may have problem with the table_name|| 'set id= part - specifically I think you should add a space between ' and set. Try to print what you're executing and you may found out where the problem may be.

answered Apr 10 '14 at 14:10

Jan Spurny

5,219
1
33
47

score 0 · Answer 3 · answered Apr 10 '14 at 14:10

Execute Procedure as below:

select update_table('my','my_table');

Also change line

execute 'update '||schema||'.'||table_name|| 'set id = substring(id from 1 for 2) where name = "test"';

to

execute 'update '||schema_name||'.'||table_name|| 'set id = substring(id from 1 for 2) where name = "test"';

Update from stored procedure

3 Answers3