How do I store a set of queries inside PostgreSQL so I can easily run it again?

Question

I want to save a set of queries (multiple SQL updates) as a single element that I can execute using pgAdmin3 (PostgreSQL 9.1).

I know that I can save single SELECTS as views but how about multiple UPDATE queries?

Example:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

Update: What I looking for is a step-by-step guide of adding a stored procedure using the GUI, not running a SQL query that creates it.

So far, I encountered two problems with "New function...": the return type is required and found that NULL is not acceptable, so tried integer. Also, set the type to SQL but I don't know what exactly to write inside the SQL tab, whatever I try the OK button is still disabled and the statusbar says: Please enter function source code.

Solution 1

Do you know or did you try stored procedure (well, stored-procedure-like functions) ?

http://www.postgresql.org/docs/9.1/interactive/plpgsql-structure.html

To call it

select <name of function>(<param1>, <param2>) as result;

Solution 2

Here is the missing guide for a basic SQL stored procedure, one that does return 1.

• right click on Functions and choose New Function...
• complete name as my_procedure, return type as integer, language as sql
• select Definition tab and write SELECT 1;
• done

It would be nice to know if you can create queries that are returning nothing.

Comments

[sorin]

Exactly, but how do I add it, run it and update it? Can you put basic example?

[sorin]

Let me explain: I tried to add a function, with language SQL, but it asks me about a return type.

[Raphaël Althaus]

Examples of creation are present in the page I gave (CREATE FUNCTION bla bla). If the function must return nothing, you can use CREATE FUNCTION blabla (parameters) RETURNS void as $$. To run it, see my edit. To update it : it must appear somewhere in treeview from pgAdmin, no (ain't got one here) ?

[Erwin Brandstetter]

@sorin: Yes, pgAdmin shows functions in the object browser in treeview. Start by reading the manual about creating functions. Proceed with the link Raphael supplied. Be aware that SQL functions are different from plpgsql functions. You can use either for your job description so far.

[nealmcb]

Thanks. Note - it looks like you mean to put SELECT 1; in the Code tab.