• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1050
  • Last Modified:

Convert SQL Server stored procedure to Postgres

Hi experts,

I would like to convert a Microsoft SQL Sever 2000 stored procedure to a postgres procedure.

The procedure inserts a null value in the Description field in the Client table, then returns the auto incrementing integer field ID.

Here's the stored procedure:

CREATE PROCEDURE GetNextClientID
@ID int OUTPUT
AS
SET NOCOUNT ON
INSERT INTO Audio ("Description")
VALUES (null)
SELECT @ID = @@IDENTITY
RETURN @ID
GO

If it's possible to input the table name and field name to enter the null value that would be fantastic, though if too hard don't worry aboutn it.

Please add your solution!
0
undyshelts
Asked:
undyshelts
  • 4
1 Solution
 
earth man2Commented:
create table audio ( id serial primary key, "Description" text );

create or replace function GetNextClientID() returns int as $$
declare
the_next_id int;
begin
  insert into Audio("Description") values ( null ) returning id into the_next_id;
  return the_next_id;
end;
$$ language plpgsql;

CREATE FUNCTION
treacle=> select GetNextClientID();
 getnextclientid
-----------------
               1
(1 row)

treacle=> select * from Audio;
 id | Description
----+-------------
  1 |
(1 row)
0
 
undysheltsAuthor Commented:
Thanks earthman2, any way to pass the table name (audio), null column (description) and PK field (ID) as variables?
0
 
earth man2Commented:
I suspect that it cannot be done.

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

"SELECT INTO is not currently supported within EXECUTE."

0
 
earth man2Commented:
But then maybe it is try.
create or replace function GetNextClientID( tablename text, colname text ) returns int as $$
declare
the_next_id int;
begin
  EXECUTE 'insert into "' || tablename||"("'|| colname || '") values ( null ) returning id' into the_next_id;
  return the_next_id;
end;
$$ language plpgsql;

0
 
earth man2Commented:
create or replace function GetNextClientID( tablename text, colname text ) returns int as $$
declare
the_next_id int;
begin
  EXECUTE 'insert into "' || tablename||'"("'|| colname || '") values ( null ) returning id' into the_next_id;
  return the_next_id;
end;
$$ language plpgsql;


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now