Solved

Convert SQL Server stored procedure to Postgres

Posted on 2007-04-07
7
1,019 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

724 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