Skip site navigation (1) Skip section navigation (2)

Re: Moving from Sybase to Postgres - Stored Procedures

From: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
To: Andre Schnoor <andre(dot)schnoor(at)web(dot)de>
Subject: Re: Moving from Sybase to Postgres - Stored Procedures
Date: 2005-01-28 22:18:42
Message-ID: 41FABA42.3080404@wardbrook.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Ralph GraulichDate: 2005-01-28 22:19:53
Subject: Rule problem with OLD / NEW record set (repost)
Previous:From: mmirandaDate: 2005-01-28 22:12:34
Subject: Re: Moving from Sybase to Postgres - Stored Procedures

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group