pl/pgSQL newbie plz help

[roberm1]

I have serveral SQL Server Stored Procedures to translate into PostgreSQL functions. I would be very grateful if some wonderfull person could give me idea of how to do this, below is a very simplest example of one of my Stored Procedures, could someone show me how this would written in PL/pgSQL, please ?

Here goes:

CREATE PROCEDURE [editcall]
(@locationin [varchar](50),
@typein [varchar](50),
@notesin [varchar](1000),
@equipment [varchar](50),
@idin [int])
AS

Declare @locationid int
Declare @typeid int
Declare @equipid int

Select @locationid = (Select id from location where ward = @locationin)
Select @typeid = (Select id from type where type = @typein)
Select @equipid = (Select id from equipment where equipment = @equipmentin)

UPDATE [CallLog].[dbo].[calls]

SET

[location] = @locationid,
[type] = @typeid,
[notes] = @notesin,
[equipment] = @equipid

WHERE
([id] = @idin)

And thats it folks, I have migrated the db to postgres, but now need to translate the Stored Procedures into Postgres functions, I am currently very stuck, any help & advice would be very much appretiated.

Thanks, Mark.

 

[Leland123]

Hi roberm1,

You might take a look at the postgres cookbook pages. Their you will find many examples of how to write stored procedures. Postgres allows procedures to be written in several languages including perl, python Tdtkl and
PL/pgSQL

http://www.brasileiro.net:8080/postgres/cookbook/

You might also take a look at the postgres doc about procedures.

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql.html

LelandJ Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox