From: | "Andre Schnoor" <andre(dot)schnoor(at)web(dot)de> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Moving from Sybase to Postgres - Stored Procedures |
Date: | 2005-01-28 21:54:15 |
Message-ID: | 002001c50583$eec527c0$c301a8c0@omen |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Joshua D. Drake" wrote:
> Andre Schnoor wrote:
> > Hi,
> >
> > I am moving from Sybase to pgsql but have problems with stored procedures.
> > The typical procedure uses
> >
> > a) named parameters,
> > b) local variable declarations and assignments
> > c) transactions
> > d) cursors, views, etc.
> >
> > I can't seem to find these things in the Postgres function syntax.
[...]
> Perhaps if you provided the actual problem? Is there a specific
> procedure that you are trying to port that you do not understand in the
> PgSQL sense?
Thank you for asking, Joshua. I've put an example procedure skeleton here:
CREATE PROCEDURE do_something
@song_id int,
@user_id int,
@method int,
@length int = 0,
@date_exact datetime,
@default_country int = null
AS
-- temporary variables
DECLARE
@artist int,
@sample int,
@date varchar(32),
@country int
BEGIN
-- assign temporary variables
select @date = convert(varchar(32),@date_exact,101)
select @artist = user_id, @sample = is_sample from sto_song where song_id = @song_id
-- perform conditional code
if (@sample = 1) begin
begin transaction
... do something ...
commit transaction
end else begin
... do something else ...
end
-- return results
select
result1 = ... some expression ...,
result2 = ... another expression ...
END
I could not yet translate this to PgSQL, as I can't find any control structures, variable declaractions, etc.
I assume this can be done through the Perl module, but I find this rather strange. I'm afraid that Perl requires to have the queries parsed and passed down each and every time, instead of having them compiled once. I also can't see the benefit of converting data objects back and forth to/from Perl while everything actually happens within Postgres.
Am I missing something important?
Greetings,
Andre
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-01-28 21:54:36 | Re: upgrading to postgresql 8 |
Previous Message | Shawn Harrison | 2005-01-28 21:53:51 | Allowing update of column only from trigger |