Solved

Convert SQL Server stored procedure to Postgres

Posted on 2007-04-07
7
999 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

774 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