Moving from Sybase to Postgres - Stored Procedures

-----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

 --- "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 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

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

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:

<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