Stored Procedure vs Function in PostgreSQL for DML Statements

postgresqlpostgresql-9.3stored-procedures

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure?

My basic steps are:

RENAME TABLE FOOBAR_1234.table1_org TO FOOBAR_1234.table1_old;
DELETE FOOBAR_1234.table1_old WHERE 1=1;
RENAME TABLE FOOBAR_1234.table1 TO FOOBAR_1234.table1_org;

What I also need is the flexibility to parameterize the ID as in 1234 with any ID, example 5678.

If you have an example, that would be helpful too.

Best Answer

There is no such thing as a stored procedure in PostgreSQL. All PostgreSQL has are SQL-callable functions defined in a number of languages - LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plperl, etc.

Additionally, there's no RENAME TABLE command. You want ALTER TABLE ... RENAME TO ....

This particular example must be written in PL/PgSQL using EXECUTE because it relies on dynamic SQL. See the numerous existing examples on Stack Overflow of using PL/pgSQL EXECUTE. Something like a PL/PgSQL function with:

EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
    'FOOBAR_'||_schema_id, 'table1_org',
    'FOOBAR_'||_schema_id, 'table1_old');

Edit: OK, more complete example:

CREATE OR REPLACE FUNCTION myfunc(_schema_id integer)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
        'FOOBAR_'||_schema_id, 'table1_org',
        'FOOBAR_'||_schema_id, 'table1_old');

    -- Now TRUNCATE the table and do your second `RENAME`
    -- in subsequent EXECUTE statements.
END;
$$;

Related Solutions

Mysql – How to create a stored procedure with several delete in(select) statements

if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

No, the statements in a stored procedure are processed sequentially, not in parallel. A stored procedure executes in a single thread, just as if you had issued the queries one after another... not simultaneously.

There are some optimizations you could make, however.

If the `titnum` table doesn't have a multi-column index on the three columns (titvalpag, titemi, titnum) together, you should probably add one.

The delete statements can be rewritten without the IN().

Use caution with this, because doing it incorrectly will remove the wrong data.

delete from mov_pedido 
where pednot in(select distinct titnum 
                  from mov_titulo 
                 where titnum like 'A4%' 
                   and titemi <='2012-01-01' and titvalpag >0);

...becomes...

DELETE p
  FROM mov_pedido p
  JOIN mov_titulo m ON m.titnum = p.pednot
 WHERE m.titnum like 'A4%' and m.titemi <='2012-01-01' and m.titvalpag >0;

This is the multi-table delete syntax. "DELETE p" will only remove the rows from "p" (an alias for mov_pedido) that match rows in "m" (mov_titulo). It won't remove the rows from "m" unless you say "DELETE p, m" -- so don't do that.

The mov_pedido table (and the others) should have an index on the column you're using to join (pednot, etc.) if they don't already.

Mysql – Stored Procedure – MySQL

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.

Best Answer

Related Solutions

Mysql – How to create a stored procedure with several delete in(select) statements

Mysql – Stored Procedure – MySQL

Related Question