Put several INSERT statements into a stored procedure

Question

I have several insert requests like below:

insert into dummy (col1,col2) SELECT 'aa',one_col FROM another_table;
insert into dummy (col1,col2) SELECT 'bb',another_col FROM another_table;

I would like to put them in a single stored procedure and run it once in a while.

Alas, I found nothing relevant on the net. I don't want to return a value therefore creating a function is useless to me. I think that using WITH will not help.

If you have any clues, I'm willing to take it.

What is the part causing you difficulty? Why don't you simply read what you say you want to do and try it? — Dan Bracuk, Jan 01 '15 at 15:06
Hey Dan, in oracle you have something like `CREATE PROCEDURE ...`. Do you have something similar in `postgres`? — Andy K, Jan 01 '15 at 15:14
No `create function..` is your way irrespective of whether you want or dont want to return anything. — SMA, Jan 01 '15 at 15:16
Hi Akmas, I don't want to return anything. Let me have a try. If I cannot do anything, I'll return with a post. Thanks. — Andy K, Jan 01 '15 at 15:17
Dan, thanks for the kick. Never done that but never too late too learn. — Andy K, Jan 01 '15 at 19:51
A subtle, but important detail: is `another_table` supposed to be the same table in multiple calls? Also, aren't there any `WHERE` conditions? And if so, do you select the same rows from `another_table` multiple times? — Erwin Brandstetter, Jan 02 '15 at 04:54
Hi Erwin, there are `where` clauses but I do not put them here because I did not think it was relevant. — Andy K, Jan 02 '15 at 09:24
@AndyK: The `WHERE` conditions are relevant, as is my first question above, your version of Postgres, table definitions, cardinalities and the general use case ... — Erwin Brandstetter, Jan 02 '15 at 13:45

score 0 · Answer 1 · answered Jan 01 '15 at 19:41

0

I figured it out by seriously reading the manual

CREATE OR REPLACE FUNCTION my_insert() 
returns void AS
$$
BEGIN
    insert into dummy (col1,col2) SELECT 'aa',one_col FROM another_table;
    insert into dummy (col1,col2) SELECT 'bb',another_col FROM another_table;
END;
$$ language plpgsql

Then execute it by typing

SELECT "my_insert"()

@Dan: Thanks for the kick. Yes I can ! : D

answered Jan 01 '15 at 19:41

Andy K

4,944
10
53
82

1

NOTE: you don't need language `plpgsql` here; it can be done in plain `sql` . – wildplasser Jan 01 '15 at 20:06
Hi @wildplasser, I just tried with `sql` instead of `plpgsql`and it is giving me an error ... – Andy K Jan 01 '15 at 20:09
Just understood your comment. Can you give your solution, please? – Andy K Jan 01 '15 at 20:18
Maybe I could, but I won't. It would probably cost me 15 minutes to set up your tables (whose defintions you did not add to your question) plus a few more to get the function into shape. Besides: you would not learn anything from copying my answer, or somebody elses. Sorry! BTW: the `FROM someothertable` seems to miss a WHERE-clause. – wildplasser Jan 01 '15 at 20:30
That's fine . My solution may not be the best but it is working. Most likely I've could use `with` but I will have someone who knows nothing about sql, so I need an almost push button. Thank you for answering, I appreciate. – Andy K Jan 01 '15 at 20:34
3

The solution to writing the function in sql as opposed to plpgsql is to remove the BEGIN and END statements. – Lucas Jan 02 '15 at 04:07

score 0 · Accepted Answer · edited May 23 '17 at 11:50

There is nothing wrong with a PL/pgSQL function, but for "a call once in a while" and only plain SQL statements, LANGUAGE sql is the more appropriate format. Detailed discussion when to use either:

Difference between language sql and language plpgsql in PostgreSQL functions

CREATE OR REPLACE FUNCTION my_insert() 
  RETURNS void AS
$func$
   WITH sel AS (SELECT one_col, another_col FROM another_table WHERE <some_condition>)
   ,   ins1 AS (INSERT INTO dummy (col1,col2) SELECT 'aa', one_col FROM sel)
   INSERT INTO dummy (col1,col2) SELECT 'bb', another_col FROM sel;
$func$ LANGUAGE sql

The CTEs can be a bit more expensive due to materializing the set from the first SELECT - or not, if that actually saves selecting the rows multiple times, like separate INSERT statements would.

You probably want all your INSERT statements in the same batch to be based off the same snapshot. In case there can be concurrent write operation, the CTEs would guarantee that, because the whole query is based on the same snapshot of all involved tables.

If concurrency is not a problem and the SELECT is cheap (or not the same), simple, separate INSERTs may be faster.

Hi Erwin, happy new year. As you have understood, I'm newbie in `pl/sql`. Can you be more explicit in your explanation, please? — Andy K, Jan 02 '15 at 07:51
"pl/sql" is the name of the procedural language in Oracle. In Postgres you have PL/pgSQL, [quite a few other procedural server-side languages](http://www.postgresql.org/docs/current/interactive/xplang.html) - or SQL functions, but not "pl/sql". You find more explanation in the [linked answer above](http://stackoverflow.com/questions/24755468/difference-between-language-sql-and-language-plpgsql-in-postgresql-functions/24771561#24771561). I added another link for "CTE". There are *many* related answers here, skim the plpgsql tag. It's often best to start with the excellent manual, I added links. — Erwin Brandstetter, Jan 02 '15 at 13:51

Put several INSERT statements into a stored procedure

2 Answers2