How to convert from sql to postgresql (stored procedure)

**doss** · Nov 24 '07, 07:03 AM

Boss,

There are no conceptes absolutly for PROCEDURE in Postgres SQL

I dont think so.

But u can make them as FUNCTIONS in Postgres SQL

Originally posted by fatmapatlar

I have to convert this script, but i didn't. This is stored procedure of sql. I need to convert to postgresql...

if it is possible please help me!
Thanks
Fatma

Code:

 
CREATE PROCEDURE usp_generatetable(@prm_tableID int, @prm_langID int)
as 
begin
	declare @table_id int, @field_id int, @match_code varchar(50), @value varchar(250), @first_field int, 
		@query_text varchar(8000), @table_identity varchar(30), @active_match_code varchar(250),
		@dil_id varchar(20), @counter int, @rec_counter int, @data_type varchar(50), @field_name varchar(50)
 
	set nocount on
 
	declare cur_field_list cursor for
		select fieldID from tbl_fields where tableID = @prm_tableID and field_isactive = 1 order by fieldID
 
 
	select @table_identity = 'temp_' + replace(convert(varchar(50), getdate(), 112) + convert(varchar(50), getdate(), 114), ':', '')
 
	set @query_text = 'create table ' + @table_identity + ' (rec_match_code varchar(20), '
 
	set @first_field = 1
 
	open cur_field_list
 
	fetch next from cur_field_list into @field_id
 
	while @@fetch_status = 0
		begin
			select @field_name = field_name, @data_type = field_datatype from tbl_fields where fieldID = @field_id
 
			if @first_field = 1
				set @query_text = @query_text + @field_name + ' ' + @data_type
			else
				set @query_text = @query_text + ', ' + @field_name + ' ' + @data_type
 
			fetch next from cur_field_list into @field_id
 
			set @first_field = 0
		end
 
	set @query_text = @query_text + ' ) '
 
	close cur_field_list
 
	deallocate cur_field_list	
 
	EXEC (@query_text)
 
	declare cur_source_data cursor for
		select fieldID, rec_match_code, rec_value
		from tbl_records
		where tableID = @prm_tableID and langID = @prm_langID
		order by rec_match_code, fieldID
 
	open cur_source_data
	--print @@cursor_rows
 
	set @active_match_code = ''
 
	fetch next from cur_source_data
		into @field_id, @match_code, @value
 
	while @@fetch_status = 0 
		begin
			if @active_match_code <> @match_code
				begin
					set @query_text = 'insert into ' + @table_identity + ' (rec_match_code) values ( ' + '''' + @match_code + '''' + ' ) '
 
					EXEC (@query_text)
 
 
					set @active_match_code = @match_code				
				end
 
			select @field_name = field_name, @data_type = field_datatype from tbl_fields where fieldID = @field_id
 
			set @query_text = 'update ' + @table_identity + ' set ' + @field_name + ' = ' + 'convert(' + @data_type + ', ' + '''' + @value + '''' + ') ' + ' where rec_match_code = ' + '''' + @match_code + ''''
 
			EXEC (@query_text)
 
			fetch next from cur_source_data
				into @field_id, @match_code, @value
		end
	close cur_source_data
 
	deallocate cur_source_data
 
 
	EXEC('select * from ' + @table_identity)
 
	set @query_text = 'if exists (select * from dbo.sysobjects where name like ' + '''' + '%' + @table_identity + '%' + '''' + ') drop table ' + @table_identity
 
	exec(@query_text) 
 
 
 
end
GO

How to convert from sql to postgresql (stored procedure)

How to convert from sql to postgresql (stored procedure)

Comment