Solved

Convert SQL Server stored procedure to Postgres

Posted on 2007-04-07
7
1,012 Views
Last Modified: 2008-09-19
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
Comment
Question by:undyshelts
  • 4
7 Comments
 
LVL 22

Accepted Solution

by:
earth man2 earned 250 total points
ID: 18875201
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
 
LVL 1

Author Comment

by:undyshelts
ID: 18879515
Thanks earthman2, any way to pass the table name (audio), null column (description) and PK field (ID) as variables?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 18897284
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
 
LVL 22

Expert Comment

by:earth man2
ID: 18897307
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
 
LVL 22

Expert Comment

by:earth man2
ID: 18897316
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What is this datetime? 1 25
SQL Query--is not excluding a segment of my data 4 18
Update one rows based on previous row 5 27
T-SQL Query - Group By Year 3 24
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question