Thread: Moving from Sybase to Postgres - Stored Procedures
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. Procedures can be as long as 20-250 lines, performing heavy data manipulation tasks, running from a few seconds up to several hours. Database size is approx. 20GB. Functions in pgsql are very limited compared to Sybase procedures, so I'll have to find a workaround somehow. Perhaps somebody can point me to examples or hints regarding this issue. Thanks in advance! Andre
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 a. http://www.postgresql.org/docs/8.0/interactive/plpgsql- declarations.html b. (same page) c. ? d. http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html#PLPGSQL-RECORDS-ITERATING This brings about a new question: Doesn't a PL/PGSQL function always execute within the context of a transaction? I'd think you can't create a transaction within one then, correct? On Jan 28, 2005, at 3:11 PM, 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. > Procedures can be as long as 20-250 lines, performing heavy data > manipulation tasks, running from a few seconds up to several hours. > Database > size is approx. 20GB. > > Functions in pgsql are very limited compared to Sybase procedures, so > I'll > have to find a workaround somehow. Perhaps somebody can point me to > examples > or hints regarding this issue. > > Thanks in advance! > > Andre > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > - ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iD8DBQFB+qHI7aqtWrR9cZoRAhf1AJ9CVvNTv0+UHtbUqxONyHIHJ67MlQCcCgfZ K3nUK2CE7Ag7fSQsaaSqStE= =UgiS -----END PGP SIGNATURE----- ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
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. Uhmmm in Postgres function/procedures are the same thing. I am unsure about named parameters but b,c,d are all available in PostgreSQL. > Functions in pgsql are very limited compared to Sybase procedures, I seriously doubt this is the case since you have the ability to use any number of languages for your procedures including plPgsql, plPython, plPerl, plPHP etc... so I'll > have to find a workaround somehow. Perhaps somebody can point me to examples > or hints regarding this issue. 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? Sincerely, Joshua D. Drake > > Thanks in advance! > > Andre > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com
Attachment
--- "Frank D. Engel, Jr." <fde101@fjrhome.net> escribió: > a. > http://www.postgresql.org/docs/8.0/interactive/plpgsql- > > declarations.html > b. (same page) > c. ? > d. > http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- > > structures.html#PLPGSQL-RECORDS-ITERATING > > > This brings about a new question: Doesn't a PL/PGSQL > function always > execute within the context of a transaction? I'd > think you can't > create a transaction within one then, correct? > AFAIK. But in pg8 you can use EXCEPTION blocks that are implemented on the subtransaction mechanism, IIRC. regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
"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
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 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 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
> 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. > Am I missing something important? > Absolutely, that is a perfectly normal SP, i use those constructors everyday, maybe you dont have searched in the docs?. Perl?, i dont think so, PL/pgSQL is all what you need, http://www.postgresql.org/docs/7.3/interactive/programmer-pl.html --- Miguel > Greetings, > Andre
<mmiranda@americatel.com.sv> schrieb im Newsbeitrag news:76E0DAA32C39D711B6EC0002B364A6FA03F0A7FD@amsal01exc01.americatel.com.sv... > > 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. > > Am I missing something important? > > > > Absolutely, that is a perfectly normal SP, i use those constructors > everyday, maybe you dont have searched in the docs?. > Perl?, i dont think so, PL/pgSQL is all what you need, > Yes, oops. I have overlooked this. It wasn't listed where I expected it, sorry. Greetings, Andre
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