Andre Schnoor wrote:
I think what you want is plpgsql (which needs to instantiated on the database in question)"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.
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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
