Re: Moving from Sybase to Postgres - Stored Procedures

"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
havethe queries parsed and passed down each and every time, instead of having them compiled once. I also can't see the
benefitof converting data objects back and forth to/from Perl while everything actually happens within Postgres. 

Am I missing something important?

Greetings,
Andre