Update using stored procedure in postgres?

Question

I want my stored procedure to allow user to update the data.

Here's the procedure :

CREATE OR REPLACE FUNCTION update_table (
   IN _table      character varying,
   IN _col_mod    character varying,
   IN _val_mod    character varying,
   IN _col_filter character varying,
   IN _val_filter character varying
)
RETURNS void
AS
$$
BEGIN
    RAISE NOTICE 'Update table %', _table;
    EXECUTE ' UPDATE ' || quote_ident(_table) || ' SET ' || quote_ident(_col_mod) || ' = $1 WHERE ' || quote_ident(_col_filter) || ' = $2'
    USING _val_mod, _val_filter;
END;
$$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

I wanna ask, is this procedure is efficient? because it looks like I just recreate the query.

And the reason why I create a procedure like this is in my office I had new policy that DBA not allowed to perform query directly into database. We must use stored procedure to do the DML also data retrieval query.

Thanks in advance .. :D

The overhead of calling one procedure to construct a string isn't huge. Executing a command in this way shouldn't add much overhead. I presume the purpose of the new office policy is to protect the production database by making sure that everything executed against it can be checked. With this in mind, your procedure does appear to be in breach of this policy as you're basically passing in half the command as arguments. — Philip Couling
– Philip Couling, Commented Mar 5, 2012 at 9:58
This is weird, if this is what the policy requires you to do it's maybe better to send your SQL in an e-mail to whoever is allowed to query the database. — tscho
– tscho, Commented Mar 5, 2012 at 22:17

Erwin Brandstetter · Accepted Answer · 2012-03-07 00:18:49Z

3

If it is 9.1 you can use format() to make it more legible:

EXECUTE format(
    'UPDATE %I SET %I = $1 WHERE %I = $2', _table, _col_mod, _col_filter
    )
USING _val_mod, _val_filter;

edited Mar 7, 2012 at 0:18

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Mar 6, 2012 at 1:20

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

Update using stored procedure in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related