[Prev] [Menu] [Next]

How to specify SQL


  • How to describe SQL

    (1) Description way

    [timespec] SQL; [pipeline][redirection][&]
    or
    [timespec] exec_sql [option] "SQL" [pipeline][redirection][&]

    You can execute SQL by only inputting SQL statement in the both of interactive environment and a shell script. You can execute SQL with a timespec, pipekine, redirection and background_job options.

    'SQL;' is changed into 'exec_sql "SQL"' at the inside of Pgbash. Therefore, 'SQL;' is equal to 'exec_sql "SQL"'.

    (2) End mark of SQL (is ';')

    End mark of SQL is surely required when you describe SQL direct. But, if you describe 'exec_sql "SQL"', End mark of SQL is not necessarily required.

    (3) The new-line in the middle of SQL statement

    You can input a new line code at the delimiter of the statement. When inputting a new line code in the interactive environment, '>' prompt is displayed. You can input SQL statement continuously at the time. When a semicolon appears, it is regarded as the end of SQL.

    (Example in the interactive environment)
    pgbash> insert into test values(111,
    > 'aaa','bbb'
    >);

    (Example in a shell script)
    insert into test values(111,
    'aaa','bbb'
    );
    

    (4) Shell variables

    You can use shell variables in SQL sattement. When you refer to a value, you must add the '$' mark at the head of the shell variable.

    ex)
    _aa='123'
    _bb='name'
    _cc='address'
    insert into test values($_aa, '$_bb', '$_cc');
    COL1='name'
    VAL1='SUZUKI ICHIRO'
    select $COL1, $COL2 from test where $COL1='$VAL1';
    SQL="select * from test"
    exec_sql "$SQL"

    If a single quotation character data exists, you must surround the data by \' .

    ex) DATA="name'123"
    select * from test where name=\'$DATA\';


  • How to execute SQL

    (1) Interactive environment

    When inputting SQL in the interactive environment, it is executed immediately. In the case of SELECT, the retrieval results are immediately displayed.
    ex)
    select * from test; | more .................. pipeline
    select * from test; &> /tmp/xx.dat .......... redirection
    select * from test; & ....................... background_job
    time select * from test; .................... timespec
    select * from test; &> /tmp/xx.dat & ........ redirection and background_job
    
    exec_sql "select * from test" | more
    exec_sql "select * from test"  &> /tmp/xx.dat 
    

    (2) Shell script

    It is performed as follows, in order to perform the same processing as the above-mentioned.

    example) "sel.bat" shell script
    #!/usr/local/bin/pgbash
    connect to postgres user postgres;
    select * from test;
    disconnect all;  ...... disconnect is not necessarily required
    
    pgbash> sel.bat | more .................. pipeline
    pgbash> sel.bat &> /tmp/xx.dat .......... redirection
    pgbash> sel.bat & ....................... background_job
    pgbash> time sel.bat .................... timespec
    pgbash> sel.bat &> /tmp/xx.dat & ........ redirection and background_job
    

    (3) Stop displaying retrieval results

    When displaying the results on the screen, if you want to stop it, you must input Ctrl+C. However, even if Ctrl+C is inputted, a screen display does not immediately stop. Dozens of lines will be displayed.

    (4) Memory over

    It may become 'memory over' if a lot of retrieval results are displayed. If you want to display a lot of results, you must add 'limit' option.

    exj
    pgbash> select * from test limit 500; | more ............. max. 500 lines
    pgbash> select * from test limit 500 offset 100; | more .. max. 500 offset 100


  • Limitation for describing SQL

    (1) Position of SQL

    You must specify SQL at the head position of the line. You can not specify SQL at the back position of 'IF/WHILE'.

    incorrect example correct example
      if SQL * from test;
      then
      ...
      fi
      
    select * from test;
    if (( SQLCODE == 0 )); then
       ...
    fi
    

    If you want to know the status after executing SQL, you can refer to 'SQLCODE' shell variable.


    (2) Reserved word

    Pgbash parses it as SQL if the next reserved word exists at the head position of the line.

      "ABORT",        "ALTER",        "ANALYZE",
      "BEGIN",
      "CHECKPOINT",   "CLOSE",        "CLUSTER",
      "COMMENT",      "COMMIT",       "CONNECT",
      "COPY",
      "CREATE",       "DECLARE",      "DELETE",
      "DISCONNECT",   "DROP",
      "END",          "EXPLAIN",      "FETCH",
      "GRANT",
      "INSERT",       "LISTEN",       "LOAD",
      "LOCK",
      "MOVE",
      "NOTIFY",
      "RESET",        "REVOKE",       "ROLLBACK",
      "SELECT",       "SET",          "SHOW",
      "TRUNCATE",
      "UNLISTEN",     "UPDATE",
      "VACUUM",
    

    Pgbash demands an input until a semicolon appears.

    (3) Interruption of the SQL input

    Please do as following when you want to interrupt the SQL input.

    a. First line : Ctrl+C
    b. Second or later line : Ctrl+D


    (4) The case in which a reserved word overlaps with a command name.

    When a program name overlaps with an SQL reservation word, you must specify a path of a program file.

    pgbash> ./ALTER
    pgbash> /home/admin/ALTER


  • Single/Double quotation in SQL statement

    It is necessary to put two single quotations to deal with one single quotation character data in SQL statement.

    ex) insert into test values(111,'aaa''aaa');

    If you want to put one single quotation to deal with one single quotation character data, you must surround the data by \' .

    ex) DATA="aaa'aaa"
    insert into test values(111,\'aaa'aaa\');
    insert into test values(111,\'$DATA\');

    You had to put \" as a double quotation until pgbash-2.4a.1, but it is not necessary to put \ in pgbash-2.4a.2.

    exj select aa as "Name", bb as "Type" from test;

  • Comment in SQL statement

    It is considered that from '#'character to line feed is a comment.
    ex)
    create table member (
    #---------------------------------------------------------------
       userid       int4                     # User code
                    primary key not null,    
    #---------------------------------------------------------------
       kind         varchar(12)              # Kind of user
                    check(kind in ('A','B')),# ('A', or 'B')
    #---------------------------------------------------------------
       name         varchar(32) not null,    # User name
    #---------------------------------------------------------------
       zip          char(8)                  # zip code
                    default '000-0000',
    #---------------------------------------------------------------
       address      varchar(64) not null,    # User address
    #---------------------------------------------------------------
       tel          varchar(16) not null,    # Telephone
    #----------------------------------------------------------------
       email        varchar(64),             # Mail address
    #----------------------------------------------------------------
       up_date      timestamp                # Update date
                    default 'now'            
    #----------------------------------------------------------------
    );
    

[Prev] [Menu] [Next]