Re: Moving from Sybase to Postgres - Stored Procedures

Andre Schnoor wrote:
> "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 think what you want is plpgsql (which needs to instantiated on the
database in question)
createlang -U postgres plgsql dbname (for example)

The documentation is pretty decent on the language itself: for example:

CREATE FUNCTION somefunction (integer, integer, timestamp) RETURNS
[setof] datatype AS $$
DECLARE
    -- alias the passed arguments
    thesong_id ALIAS FOR $1;
    theuser_id ALIAS FOR $2;
    datetime ALIAS FOR $3;
    -- temporary variables
    artist int;
    sample int;
    thedate date;
BEGIN
    thedate := datetime::date;
    SELECT INTO artist user_id from sto_song where song_id = thesong_id;
    SELECT INTO sample is_sample from sto_song where song_id = thesong_id;
    IF sample = 1 THEN
        -- do stuff
    ELSE
        -- do other stuff
    END IF;
    RETURN something;
END;
$$ LANGUAGE plpgsql;

See if that helps you ... it really looks as though the languages are
similar enough that moving the stored procedures should a fairly decent
proposition.

Sven