PostgreSQL 関数 — プログラミング言語 Ring 1.14 取扱説明書

Skip to content

# PostgreSQL 関数

PostgreSQL データベースの用法です。

## ライブラリの読み込み

この関数の使用前に postgresqllib.ring ライブラリを読み込みます。

    load "postgresqllib.ring"
    # PostgreSQL 関数の使用

## 用例

用例 (1):

    load "postgresqllib.ring"

    conninfo = "user=postgres password=sa dbname = postgres"

    exit_nicely = func conn {
            PQfinish(conn)
            shutdown(1)
    }

    conn = PQconnectdb(conninfo)

    if (PQstatus(conn) != CONNECTION_OK)
            fputs(stderr, "Connection to database failed: "+PQerrorMessage(conn))
                    call exit_nicely(conn)
    ok

    res = PQexec(conn, "select * from pg_database")
    if PQresultStatus(res) != PGRES_TUPLES_OK
            fputs(stderr, "Select failed: " + PQerrorMessage(conn))
            PQclear(res)
            exit_nicely(conn)
    ok

    nFields = PQnfields(res)
    for i = 1 to nFields
                    ? PQfname(res, i-1)
    next

    ? copy("*",60)

    for i = 1 to PQntuples(res)
            for j=1 to nFields
                    see PQgetvalue(res, i-1, j-1) + " "
            next
            see nl
    next

    PQclear(res)

    PQfinish(conn)

実行結果:

    datname
    datdba
    encoding
    datcollate
    datctype
    datistemplate
    datallowconn
    datconnlimit
    datlastsysoid
    datfrozenxid
    datminmxid
    dattablespace
    datacl
    ************************************************************
    postgres 10 6 English_United States.1252
            English_United States.1252 f t -1 12937 549 1 1663
    template1 10 6 English_United States.1252 English_United States.1252
            t t -1 12937 549 1 1663 {=c/postgres,postgres=CTc/postgres}
    template0 10 6 English_United States.1252 English_United States.1252
            t f -1 12937 549 1 1663 {=c/postgres,postgres=CTc/postgres}
    mahdb 10 6 English_United States.1252 English_United States.1252
            f t -1 12937 549 1 1663

用例 (2):

    load "postgresqllib.ring"

    conninfo = "user=postgres password=sa dbname = mahdb"

    exit_nicely = func conn {
            PQfinish(conn)
            shutdown(1)
    }

    conn = PQconnectdb(conninfo)

    if (PQstatus(conn) != CONNECTION_OK)
            fputs(stderr, "Connection to database failed: "+PQerrorMessage(conn))
                    call exit_nicely(conn)
    ok

    res = PQexec(conn, "
            DROP DATABASE mahdb;
    ")
    if PQresultStatus(res) != PGRES_TUPLES_OK
            fputs(stderr, "Remove failed: " + PQerrorMessage(conn))
            PQclear(res)
    ok
    PQclear(res)

    res = PQexec(conn, "CREATE DATABASE mahdb;")
    if PQresultStatus(res) != PGRES_TUPLES_OK
            fputs(stderr, "Create database failed: " + PQerrorMessage(conn))
            PQclear(res)
    ok

    res = PQexec(conn, "
    CREATE TABLE COMPANY (
                     ID INT PRIMARY KEY     NOT NULL,
                     NAME           TEXT    NOT NULL,
                     AGE            INT     NOT NULL,
                     ADDRESS        CHAR(50),
                     SALARY         REAL );
    ")
    if PQresultStatus(res) != PGRES_TUPLES_OK
            fputs(stderr, "Create Table failed: " + PQerrorMessage(conn))
            PQclear(res)
    ok
    PQclear(res)

    res = PQexec(conn, "
                    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
                    VALUES  (1, 'Mahmoud' , 31, 'Jeddah', 10.00 ),
                                    (2, 'Ahmed'   , 27, 'Jeddah', 20.00 ),
                                    (3, 'Mohammed', 33, 'Egypt' , 30.00 ),
                                    (4, 'Ibrahim' , 24, 'Egypt ', 40.00 );
    ")
    if PQresultStatus(res) != PGRES_TUPLES_OK
            fputs(stderr, "Insert Table failed: " + PQerrorMessage(conn))
            PQclear(res)
    ok
    PQclear(res)

    res = PQexec(conn, "
               select * from COMPANY
    ")
    if PQresultStatus(res) != PGRES_TUPLES_OK
            fputs(stderr, "Select failed: " + PQerrorMessage(conn))
            PQclear(res)
            call exit_nicely(conn)
    ok

    nFields = PQnfields(res)
    for i = 1 to nFields
                    ? PQfname(res, i-1)
    next

    ? copy("*",60)

    for i = 1 to PQntuples(res)
            for j=1 to nFields
                    see PQgetvalue(res, i-1, j-1) + " "
            next
            see nl
    next

    PQclear(res)

    PQfinish(conn)

実行結果:

    id
    name
    age
    address
    salary
    ************************************************************
    1 Mahmoud  31 Jeddah  10
    2 Ahmed    27 Jeddah  20
    3 Mohammed 31 Egypt   30
    4 Ibrahim  24 Egypt   40

## RingPostgreSQL 定数

この定数は RingPostgreSQL ライブラリで定義済みです。

    CONNECTION_STARTED
    CONNECTION_MADE
    CONNECTION_AWAITING_RESPONSE
    CONNECTION_AUTH_OK
    CONNECTION_SSL_STARTUP
    CONNECTION_SETENV
    CONNECTION_OK

    PQPING_OK
    PQPING_REJECT
    PQPING_NO_RESPONSE
    PQPING_NO_ATTEMPT

    PGRES_EMPTY_QUERY
    PGRES_COMMAND_OK
    PGRES_TUPLES_OK
    PGRES_COPY_OUT
    PGRES_COPY_IN
    PGRES_BAD_RESPONSE
    PGRES_NONFATAL_ERROR
    PGRES_FATAL_ERROR
    PGRES_COPY_BOTH
    PGRES_SINGLE_TUPLE

    PG_DIAG_SEVERITY
    PG_DIAG_SQLSTATE
    PG_DIAG_MESSAGE_PRIMARY
    PG_DIAG_MESSAGE_DETAIL
    PG_DIAG_MESSAGE_HINT
    PG_DIAG_STATEMENT_POSITION
    PG_DIAG_INTERNAL_POSITION
    PG_DIAG_INTERNAL_QUERY
    PG_DIAG_CONTEXT
    PG_DIAG_SCHEMA_NAME
    PG_DIAG_TABLE_NAME
    PG_DIAG_COLUMN_NAME
    PG_DIAG_DATATYPE_NAME
    PG_DIAG_CONSTRAINT_NAME
    PG_DIAG_SOURCE_FILE
    PG_DIAG_SOURCE_LINE
    PG_DIAG_SOURCE_FUNCTION

## RingPostgreSQL 関数

この関数は RingPostgreSQL ライブラリで定義されています。

リファレンス : https://www.postgresql.org/docs/9.1/static/libpq.html

    PGconn *PQconnectdbParams(const char **keywords,
            const char **values,int expand_dbname);
    PGconn *PQconnectdb(const char *conninfo)
    PGconn *PQsetdbLogin(const char *pghost,const char *pgport,
            const char *pgoptions,const char *pgtty,
            const char *dbName,const char *login,const char *pwd)
    PGconn *PQsetdb(char *pghost,char *pgport,char *pgoptions,
            char *pgtty,char *dbName)
    PGconn *PQconnectStartParams(const char **keywords,
            const char **values,int expand_dbname)
    PGconn *PQconnectStart(const char *conninfo)
    PostgresPollingStatusType PQconnectPoll(PGconn *conn)
    PQconninfoOption *PQconndefaults(void)
    PQconninfoOption *PQconninfo(PGconn *conn)
    PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg)
    void PQfinish(PGconn *conn)
    void PQreset(PGconn *conn)
    int PQresetStart(PGconn *conn)
    PostgresPollingStatusType PQresetPoll(PGconn *conn)
    PGPing PQpingParams(const char **keywords,const char **values,
            int expand_dbname)
    PGPing PQping(const char *conninfo)
    char *PQdb(const PGconn *conn)
    char *PQuser(const PGconn *conn)
    char *PQpass(const PGconn *conn)
    char *PQhost(const PGconn *conn)
    char *PQport(const PGconn *conn)
    char *PQtty(const PGconn *conn)
    char *PQoptions(const PGconn *conn)
    ConnStatusType PQstatus(const PGconn *conn)
    PGTransactionStatusType PQtransactionStatus(const PGconn *conn)
    const char *PQparameterStatus(const PGconn *conn, const char *paramName)
    int PQprotocolVersion(const PGconn *conn)
    int PQserverVersion(const PGconn *conn)
    char *PQerrorMessage(const PGconn *conn)
    int PQsocket(const PGconn *conn)
    int PQbackendPID(const PGconn *conn)
    int PQconnectionNeedsPassword(const PGconn *conn)
    int PQconnectionUsedPassword(const PGconn *conn)
    int PQsslInUse(const PGconn *conn)
    const char *PQsslAttribute(const PGconn *conn, const char *attribute_name)
    const char **PQsslAttributeNames(const PGconn *conn)
    void *PQsslStruct(const PGconn *conn, const char *struct_name)
    void *PQgetssl(const PGconn *conn)
    PGresult *PQexec(PGconn *conn, const char *command);
    PGresult *PQexecParams(PGconn *conn,const char *command,int nParams,
            const Oid *paramTypes,const char **paramValues,
            const int *paramLengths,const int *paramFormats,int resultFormat)
    PGresult *PQprepare(PGconn *conn,const char *stmtName,
            const char *query,int nParams,const Oid *paramTypes)
    PGresult *PQexecPrepared(PGconn *conn,const char *stmtName,
            int nParams,const char **paramValues,
            const int *paramLengths,const int *paramFormats,int resultFormat)
    PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName)
    PGresult *PQdescribePortal(PGconn *conn, const char *portalName)
    ExecStatusType PQresultStatus(const PGresult *res)
    char *PQresStatus(ExecStatusType status)
    char *PQresultErrorMessage(const PGresult *res)
    char *PQresultErrorField(const PGresult *res, int fieldcode)
    void PQclear(PGresult *res)
    int PQntuples(const PGresult *res)
    int PQnfields(const PGresult *res)
    char *PQfname(const PGresult *res,int column_number)
    int PQfnumber(const PGresult *res,const char *column_name)
    Oid PQftable(const PGresult *res,int column_number)
    int PQftablecol(const PGresult *res,int column_number)
    int PQfformat(const PGresult *res,int column_number)
    Oid PQftype(const PGresult *res,int column_number)
    int PQfmod(const PGresult *res,int column_number)
    int PQfsize(const PGresult *res,int column_number)
    int PQbinaryTuples(const PGresult *res)
    char *PQgetvalue(const PGresult *res,int row_number,int column_number)
    int PQgetisnull(const PGresult *res,int row_number,int column_number)
    int PQgetlength(const PGresult *res,int row_number,int column_number)
    int PQnparams(const PGresult *res)
    Oid PQparamtype(const PGresult *res, int param_number)
    void PQprint(FILE *fout,const PGresult *res,const PQprintOpt *po)
    char *PQcmdStatus(PGresult *res)
    char *PQcmdTuples(PGresult *res)
    Oid PQoidValue(const PGresult *res)
    char *PQoidStatus(const PGresult *res)
    char *PQescapeLiteral(PGconn *conn, const char *str, size_t length)
    char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length)
    size_t PQescapeStringConn(PGconn *conn,char *to,
            const char *from, size_t length,int *error)
    size_t PQescapeString(char *to, const char *from, size_t length)
    unsigned char *PQescapeByteaConn(PGconn *conn,
            const unsigned char *from,size_t from_length,size_t *to_length)
    unsigned char *PQescapeBytea(const unsigned char *from,
            size_t from_length,size_t *to_length)
    unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length)
    int PQsendQuery(PGconn *conn, const char *command)
    int PQsendQueryParams(PGconn *conn,const char *command,
            int nParams,const Oid *paramTypes,const char **paramValues,
            const int *paramLengths,const int *paramFormats,int resultFormat)
    int PQsendPrepare(PGconn *conn,const char *stmtName,
            const char *query,int nParams,const Oid *paramTypes)
    int PQsendQueryPrepared(PGconn *conn,const char *stmtName,
            int nParams,const char **paramValues,
            const int *paramLengths,const int *paramFormats,int resultFormat)
    int PQsendDescribePrepared(PGconn *conn, const char *stmtName)
    int PQsendDescribePortal(PGconn *conn, const char *portalName)
    PGresult *PQgetResult(PGconn *conn)
    int PQconsumeInput(PGconn *conn)
    int PQisBusy(PGconn *conn)
    int PQsetnonblocking(PGconn *conn, int arg)
    int PQisnonblocking(const PGconn *conn)
    int PQflush(PGconn *conn)
    int PQsetSingleRowMode(PGconn *conn)
    PGcancel *PQgetCancel(PGconn *conn)
    void PQfreeCancel(PGcancel *cancel)
    int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize)
    int PQrequestCancel(PGconn *conn)
    PGresult *PQfn(PGconn *conn,int fnid,int *result_buf,
            int *result_len,int result_is_int,const PQArgBlock *args,int nargs)
    PGnotify *PQnotifies(PGconn *conn)
    int PQputCopyData(PGconn *conn,const char *buffer,int nbytes)
    int PQputCopyEnd(PGconn *conn,const char *errormsg)
    int PQgetCopyData(PGconn *conn,char **buffer,int async)
    int PQgetline(PGconn *conn,char *buffer,int length)
    int PQgetlineAsync(PGconn *conn,char *buffer,int bufsize)
    int PQputline(PGconn *conn,const char *string)
    int PQputnbytes(PGconn *conn,const char *buffer,int nbytes)
    int PQendcopy(PGconn *conn)
    int PQclientEncoding(const PGconn *conn)
    char *pg_encoding_to_char(int encoding_id)
    int PQsetClientEncoding(PGconn *conn, const char *encoding)
    void PQtrace(PGconn *conn, FILE *stream)
    void PQuntrace(PGconn *conn)
    void PQfreemem(void *ptr)
    void PQconninfoFree(PQconninfoOption *connOptions)
    char *PQencryptPasswordConn(PGconn *conn, const char *passwd,
             const char *user, const char *algorithm)
    char *PQencryptPassword(const char *passwd, const char *user)
    PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status)
    int PQfireResultCreateEvents(PGconn *conn, PGresult *res)
    PGresult *PQcopyResult(const PGresult *src, int flags)
    int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs)
    int PQsetvalue(PGresult *res, int tup_num, int field_num,
                    char *value, int len)
    void *PQresultAlloc(PGresult *res, size_t nBytes)
    int PQlibVersion(void)
    PQnoticeReceiver PQsetNoticeReceiver(PGconn *conn,
                    PQnoticeReceiver proc,void *arg)
    PQnoticeProcessor PQsetNoticeProcessor(PGconn *conn,
                    PQnoticeProcessor proc,void *arg)
    void PQinitOpenSSL(int do_ssl, int do_crypto)
    void PQinitSSL(int do_ssl)
    int PQisthreadsafe(void)