[Prev] [Menu] [Next]

Output of the retrieval results


  • Display the retrieval results immediately

    (1) Interactive environment

    When SELECT is executed in the interactive environment, the retrieval results will be displayed immediately.

    ex)
    pgbash> connect to postgres_db user xxx NULL;
    pgbash> select * from friends;
     user_id | name     | zip      | pref_id | address      
    ---------+----------+----------+---------+------------
          22 | xxx.mmm  | 611-2222 |      77 | kobe-shi
          23 | vvvvvvv  | 622-3333 |      75 | osaka-shi
          24 | gggggg   | 633-4444 |      77 | tokyo
    ....
    (12 rows)
    

    (2) Shell script

    When SELECT is executed in a shell script, the retrieval results will be displayed immediately. This is the same as the interactive environment.

    ex)
    ("go.bat" file)
    #!/usr/local/bin/pgbash
    connect to postgres_db user xxx NULL;
    select * from from friends;
    

    pgbash> chmod +x go.bat
    pgbash> go.bat


  • Substitute the retrieval result into the shell variable

    By using 'SELECT INTO :host_variable' or 'FETCH INTO :host_variable', the retrieval result can be substituted to the shell variable.
    SELECT  col1, col2, ..
       [INTO :host_var1 [[INDICATOR] :ind_var1], ...
                :host_varN [[INDICATOR] :ind_varN]] 
          ..
    
    FETCH [option] [IN] cursor_name
       [INTO :host_var1 [[INDICATOR] :ind_var1], ...
                :host_varN [[INDICATOR] :ind_varN]]; 
    
    host_var...... host variable
    ind_var....... indicator variable(NULL is "1", NOT NULL is "0")
    

    ex)
    select col1, col2 into :col1, :col2 from test where code='111';
    select version() into :version;
    select count(*) into  :count  from test;
    
    begin; 
    declare cur cursor for select * from test;
    declares -i x ; let x=0;  while ((x < $count))  
    do
       FETCH IN CUR INTO :code INDICATOR :ind_c,
                         :name:ind_n, :address :ind_a;
        if (( SQLCODE == SQL_OK )); then
            if ((ind_n != SQL_NULL || ind_a != SQL_NULL)); then
                echo "$code, $name, $address"
                let x=x+1
            fi
        else
            if(( SQLCODE == SQL_NOT_FOUND )); then
                    let x=11
            else
                    echo "$SQLERRMC"
                    let x=x+1
            fi
        fi
    done
    end;
    

    When using INTO clause, the retrieval result has to be only one row. If the retrieval results are many rows, the retrieval results are displayed to the standard output.

    Here, SQLCODE means a SQL error code and $SQLERRMC means a shell variable which shows a SQL error message. In addition, SQL_NULL and SQL_NOT_FOUNF is a default value which Pgbash sets up. (see error code) It is better to use an English small letter for a host variable. Because Pgbash use English capital letter as shell variable names.

    # In the (( )) operation style, it is possible to omit head '$ ' of the integer type shell variable.


  • Display the retrieval results in every one page

    Pgbash outputs the retrieval results continuously. Therefore, if you want to stop displaying continuously, you must add the pipe '|' and 'more'.

    ex)
    pgbash> select * from test; | more (or less)

  • Write the retrieval results to the file.

    It is very easy to write the retrieval results to the file by Pgbash. "&> file name" is added behind "SQL;".

    ex)
    pgbash> select * from test; &> /tmp/sel.dat


[Prev] [Menu] [Next]