[SQL] Postgresql Function

postgresql . i know how to do it in MS-SQL , i also have that code .if you dont understand what am trying to say . i can post sql code .thanks REHAN SALEEM 

[SQL] PostgreSQL Function

hi  i want to create a function in postgresql that take input for columns from user for example first_name , last_name, addres. and will put them into table , and i also want to use exception if user enters wrong data. will some one help me how can i create thats function , because i am new to p

[SQL] how to return whole table from Function not just the id integer column

hi , how can i return the whole user table from this function not just the id . thanks CREATE TABLE users(id serial PRIMARY KEY, first_name varchar(10), last_name varchar(10)); CREATE OR REPLACE FUNCTION new_user(fname varchar, lname varchar) RETURNS int AS $$ DECLARE r int; BEGIN -- custom except

[SQL] How to Return Table From Function

hi , i have created this function CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg integer ,center_distance integer) RETURNS varchar AS $$ DECLARE percentage record; BEGIN select fname, lname, count(userid) totalcount ,100.00*count(useriddetails)/totaluser into percentage

[SQL] MS-SQL Store Procedure to Postgresql Function

hi , how i can convert this store procedure to PostgreSQL function, especially I really dont know how to set type to readonly in PostgreSQL. thanks ALTERPROCEDURE [dbo].[sp_DeleteUserData]   @ACDetailsID dbo.ACdetailsID_type READONLY   AS   DECLARE@ID int begintry begintransaction    D

[SQL] Calling Postgresql Function to delete user data

Hi , I have created this function to remove UserDataAccountid from both tables UserAccountDetails and UserAC CREATE OR REPLACE FUNCTION DeleteUserData(ACDetailsID int[]) RETURNS void AS $$ DECLARE _id int; BEGIN   -- postgresql has no table type, use a int array instead   FOR _id IN array_lower(AC

[SQL] SQL View to PostgreSQL View

Hi , I am trying to convert sql view to postgresql view but i am getting the following error i dont know how to handle dbo. in postgresql and when i remove dbo. from table name then view got created but it does not show any data, while this is working perfectly fine in sql, here is my code and er

[SQL] How to convert SQL store procedure to Postgresql function

hi , how can i convert this sql store procedure to postgresql function , i shall be very thankful to you, as i am new to postgresql and i dont know how to handle this kind of store procedure in postgresql thanks ALTERPROCEDURE [dbo].[sp_GetUserByID]   @UserId varchar(50),   @KBId  varch

Re: [SQL] How to convert SQL store procedure to Postgresql function

r_ +'''' if (centre_distance<>'') set sql += ' and (centredistance<=' + centre_distance + ' or ' + centre_distance + '=1) ' set sql += ' order by chr_u, start_u' exec(sql) end

Re: [SQL] How to convert SQL store procedure to Postgresql function

ce<>'') set sql += ' and (centredistance<=' + centre_distance + ' or ' + centre_distance + '=1) ' set sql += ' order by chr_u, start_u' exec(sql) end; $BODY$ language plpgsql; Fr

[SQL] How to shrink database in postgresql

hi , how can i shrink database in postgresql here is a MS-SQL store procedure which shrinks the database. how same task can be achieved in postgresql. ALTER PROCEDURE [dbo].[sp_CleanUpDB] AS declare @db nvarchar(50) select @db = db_name() DBCC SHRINKDATABASE (@db, 10) thanks

[SQL] How To Create Temporary Table inside a function

hi everyone , how can i create temp table say table1 with three column of types varchar , int and int, inside the function body and if that same table already exist it should drop that already existing table(table1) , and on every run this process should run. and how can i insert values from pos

[SQL] Invalid syntax for integer

hi , what is wrong with this if statement in this function if distance ='' THEN     distance := 1;     end if; here distance variable is of type integer , this function has been created successfully but when i execute this function it gives me this error , ERROR:  invalid input syntax for intege

[SQL] Postgresql function which compares values from both tables

hi , how to write a function which should read data from 2 tables having same number of columns and should show the common values from those tables. thanks

[SQL] how to concatenate in PostgreSQL

hi , how can we concatinate these lines and execute sql command          setsql ='select user,username, firstname '   set sql +=' lastname, cardno from table1where userid='+ 5   exec(sqi)      where 5 is the userid from table1 thanks

[SQL] MS-SQL to PostgreSql

hi, i am trying to convert this mssql store procedure to postgresql function but it is not giving me the desired output ,this function is returning a table and you dont need to worry about what it is returning all i concern the body part of the function how to transform the ms-sql code into post

[SQL] Postgresql function with temporary tables

ot;end" <= kb."end" and a.start <= kb.start     then (a."end" - kb.start)  when a."end" >= kb."end" and a.start >= kb.start     then (kb."end" - a.start)   end <= bp_overlap  ; truncate table tbltfsites1; insert into tbltfsites1 (chr, start,"end") select chr, start,"end" from tbltfsites2;    end if; exit when c_tfdetailsid is null; close c_tfdetailsid;         return query select chr , start , "end" from tbltfsites1 ;         end; $BODY$ LANGUAGE plpgsql; regards Rehan Saleem