PostgreSQL :- Load text data through a View using “Copy” statement

Background

Some database platforms allow one to load data through views.

Error

Upon creating a view across our simple table and attempting to load data through the view, prompted with a guiding error message.

Error Message

Error occurred during SQL script execution

Reason:
SQL Error [42809]: ERROR: cannot copy to view "vw_kjv"
  Hint: To enable copying to a view, provide an INSTEAD OF INSERT trigger.

Error Image

insteadOfTriggerNeeded.01.20190817.1009PM.PNG

Remediation

Outline

  1. Create/Review view definition
  2. Create a function
  3. Create a trigger
    • Bind the trigger to the function we created

Tasks

View

View Definition

create schema if not exists bible;

create or replace view bible.vw_kjv
(
      bookID
    , chapterID
    , verseID
    , verse
)
as

select
      bookID
    , chapterID
    , verseID
    , verse    

from  bible.kjv
;

Function

Function Definition

create schema if not exists bible;

CREATE OR REPLACE FUNCTION bible.function_vw_kjv_insert()
RETURNS trigger
as
$func$
begin

   /*
    * Add record into bible.book
    * a) if book is not yet registered
   */
   INSERT INTO bible.book
   (
           bookAbbrev
         , bookName
   )
   select
           new.bookid
         , new.bookid

  where not exists
                (
                    select 1
                    from   bible.book tblB
                    where  tblB.bookabbrev = new.bookid
                )
   ;

   /*
    * Add record into bible.kjv
   */
   INSERT INTO bible.kjv
   (
          bookid
        , chapterid
        , verseid
        , verse
   )
   VALUES
   (
          new.bookid
        , new.chapterid
        , new.verseid
        , new.verse
    );

    /*
        Return new record
    */
    return new;

end;

$func$ 

LANGUAGE plpgsql;
;

COMMENT ON FUNCTION bible.function_vw_kjv_insert
    IS
    'Function that manages instead of trigger/insert on vw_kjv'
    ;

Trigger

Trigger Definition


drop trigger if exists tr_vw_kjv_insert on bible.vw_kjv
;

create trigger tr_vw_kjv_insert
    instead of insert on bible.vw_kjv
    for each row
    EXECUTE procedure bible.function_vw_kjv_insert ()
    ;

COMMENT ON TRIGGER tr_vw_kjv_insert on bible.vw_kjv
    IS
    'Instead of trigger/insert on vw_kjv'
    ;   

Copy Statement

Sample Execution


/*
    On bible.kjv => Remove Existing Data
*/
truncate table bible.kjv
     RESTART identity
;

/*
    On bible.kjv => Restart identity column
*/
ALTER TABLE bible.kjv
    ALTER COLUMN id
        RESTART WITH 1
;

/*
    On bible.kjv => Copy Data

        a) Specify columns
        b) Delimiter is |
*/
COPY bible.vw_kjv
(
      bookID

    , chapterID

    , verseID  

    , verse
)

FROM 'C:\sample\sample.db\bible\textfile\kjv\kjvdat.txt'

delimiter AS '|'

;

/*
    On bible.kjv => Copy Data

        a) Column verse

            i) Remove leading spaces
**/
update bible.vw_kjv
set    verse = ltrim(verse)
;

/*
    On bible.kjv => Update statistics
*/
analyze bible.kjv
;

Summary

One of the good things about PostgreSQL is that the error messages are very good and guiding.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s