October 16, 2017

Postgresql Insert into Multiple Tables using void.


When you think you know everything here comes something simple to keep your hands dirty, a developer try to translate a ms sql procedure into a postgresql function and eventually it was pass to me, so I wrote something similar to below with a special caveat.
Enjoy....

CREATE OR REPLACE FUNCTION SchemaName.FunctionName_Save(
                _parameter1 character varying DEFAULT NULL::character varying,
                _parameter2 character varying DEFAULT NULL::character varying,
                _parameter3 timestamp without time zone DEFAULT NULL::timestamp without time zone,
                __parameter4 timestamp without time zone DEFAULT NULL::timestamp without time zone,
                _parameter5 character varying DEFAULT NULL::character varying,
                _parameter6 character varying DEFAULT NULL::character varying,
                _parameter7 character varying DEFAULT NULL::character varying,
                _parameter8 character varying DEFAULT NULL::character varying,
                _parameter9 character varying DEFAULT NULL::character varying,
                _parameter0 character varying DEFAULT NULL::character varying,
                _time timestamp without time zone DEFAULT NULL::timestamp without time zone,
                _result character varying DEFAULT NULL::character varying,
                _details character varying DEFAULT NULL::character varying,
                _queue character varying DEFAULT NULL::character varying)
RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE

AS $BODY$

           declare
             _local01 int;
            _local02 int;
            _local03 int;
            _local04 int;
            _local05 bigint;
            _local06 bigint;
            _local07 bigint;
begin


    if not exists (  select 1 from SchemaName.TableName0 where lower (name) = lower(_parameter1) )
     THEN
               
                                insert into SchemaName.TableName0 (name,insertdate)
                                 values (_parameter1,__parameter4)
             returning SchemaName.TableName0.TableName0id into _local01;
      else
                 select SchemaName.TableName0.TableName0id into _local01 from  SchemaName.TableName0 where lower (ColumnName) = lower(_parameter1);
     end if;
    
    if not exists (  select 1 from  SchemaName.TableName01 where lower (ColumnName) = lower(_parameter2) )
     THEN
               
                                 insert into SchemaName.TableName01(name)
                                values (_parameter2)
             returning SchemaName.TableName01.TableName01id into _local02;
      else
                 select SchemaName.TableName01.TableName01id into _local02 from  SchemaName.TableName01 where lower (name) = lower(_parameter2);
     end if;
    
     if not exists (  select 1 from  SchemaName..TableName02 where lower (name) = lower(_parameter5) )
     THEN
               
                                 insert into SchemaName..TableName02(name)
                                values (_parameter5)
             returning SchemaName..TableName02..TableName02id into _local03;
      ELSE
        select SchemaName..TableName02..TableName02id into _local03 from  SchemaName..TableName02 where lower (ColumnName) = lower(_parameter5);
     end if;
    
     if not exists( select 1 from SchemaName.queue  where lower (queueaddress) = lower(_queue))
     then
        insert into SchemaName.queue(column,column)
       values (_queue,__parameter4)
       returning SchemaName.queue.queueid into _local04;
      else
        select SchemaName.queue.queueid into _local04 from SchemaName.queue  where lower (ColumnName) = lower(_queue);
     end if;
    
    _local05 := nextval('SchemaName.TableName04_seq'::regclass);
   
    INSERT INTO SchemaName.TableName04(
                   Columns,columns,columns)
       values
       ( _local02,_parameter6,_parameter3e,__parameter4,_local04);
      --- returning  SchemaName.TableName04.TableName04id into _local05;
      
       _local06 := nextval('SchemaName.TableName04_local06_seq'::regclass);
      INSERT INTO SchemaName.TableName04details(columns,columns,columns)
                    values( _local05,_parameter7,_parameter8,__parameter4)
       returning SchemaName.TableName04details.TableName04detailsid into _local06;
      
       INSERT INTO SchemaName..TableName06
    ( columns,columns,columns,columns,...)
    VALUES
    ( _local01, _parameter0, _parameter9, _local05, _Time, _local03, _Result, _Details);
  
end;

$BODY$;


ALTER FUNCTION SchemaName.FunctionName_Save(character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, character varying, character varying)
    OWNER TO db_owner;

GRANT EXECUTE ON FUNCTION SchemaName.FunctionName_Save(character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, character varying, character varying) TO db_datawriter;

GRANT EXECUTE ON FUNCTION SchemaName.FunctionName_Save(character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, character varying, character varying) TO PUBLIC;

GRANT EXECUTE ON FUNCTION SchemaName.FunctionName_Save(character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, character varying, character varying) TO db_executor;

GRANT EXECUTE ON FUNCTION SchemaName.FunctionName_Save(character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, character varying, character varying) TO db_owner;

No comments:

Post a Comment

Contact Form

Name

Email *

Message *