Thread: Storing Procedures / Transactions

Storing Procedures / Transactions

From
"Christian Kasprowicz"
Date:
Mabye I made myself not clear enough- sorry for that...
What I want is having a statement like:
 
PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))
BEGIN
   ---check if something is valid
   ---compute something
   ---store values I got via THIS query and put them in table A, B and C
   ---see wether everything is ok
COMMIT;
 
...which I execute from a client like: exec MyProcedure(Value1, Value2, Value3)
 
Hope someone can talk me through this… ;)
 
Thanks in advance,
Chris

 

Re: Storing Procedures / Transactions

From
"Merlin Moncure"
Date:
On 10/9/06, Christian Kasprowicz <chris@feel-free.de> wrote:
> PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))
> BEGIN
>    ---check if something is valid
>    ---compute something
>    ---store values I got via THIS query and put them in table A, B and C
>    ---see wether everything is ok
> COMMIT;
>
> ...which I execute from a client like: exec MyProcedure(Value1, Value2,
> Value3)
>
> Hope someone can talk me through this… ;)

documentation is your friend :  You should read through section V.
'Extending SQL'  PostgreSQL has very rich extensibility features.
IMO, you should focus on pl/pgsql.

create or replace function foo(a int, b text) returns void as
$$
  declare
    valid boolean;
    something numeric;
    values record;
    ok boolean default true;
  begin
    select into valid f1 = a where f2 = b;
    something := 3 + a;
    for values in select * from bar order by z loop
      if values.v >= something then
        ok := false;
      end if;
      update foobar set x = x + 1set where key = b;
    end loop;
    return;
  end;
$$ language plpgsql;

merlin