Have you tried looking at this section of the manual?
http://www.postgresql.org/docs/7.4/interactive/plpgsql.html
It details all the PL/pgSQL language constructs - I found it fine when converting from Oracle to Postgres...
Just make sure you have installed the pl/pgsql language in template1 or your database before you try using it - see http://www.postgresql.org/docs/7.4/interactive/app-createlang.html or http://www.postgresql.org/docs/7.4/interactive/sql-createlanguage.html
Or type /usr/local/pgsql/bin/createlang plpgsql template1 to install the language into template1, then create your database. Or install directly into your database...
Hope that helps.
John Sidney-Woollett
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 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
